Creating SQL statements in Access

Databases
1

I’m being asked to create a couple of SQL statements to retrieve information about a Luxury Hotel Database.

List all the rooms in each hotel that have never been reserved in order by hotel number.
I have the SQL statement retrieved the rooms what have been reserved but my problem is retrieving those that have not been reserved. SELECT RoomNo FROM RESERVATION WHERE RoomNo ORDER BY HotelNo

List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

Here are the tables we are working on.

HOTEL (HotelNo, HotelName, City)

ROOM_TYPE (RoomType, Descr, RoomRate)

ROOM (HotelNo, RoomNo, RoomType, PhoneExt)
FK1: Foreign key HotelNo references HOTEL
FK2: Foreign key RoomType references ROOM_TYPE
GUEST (GuestNo, FirstName, LastName, Address, City, State, ZipCode)
GUEST_PHONE (PhoneNumber, GuestNo, PhoneType)
FK: Foreign key GuestNo references GUEST

RESERVATION (ResNum, HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
FK1: Foreign Key (HotelNo, RoomNo) references ROOM
FK2: Foreign Key GuestNo references GUEST

2

[quote=“Nancy_Vallejo, post:1, topic:114884, full:true”]
I’m being asked to create a couple of SQL statements to retrieve information about a Luxury Hotel Database.[/quote]this is homework

you’ll have to actually try to answer the question, and if your attempt doesn’t work, show what you did and explain why it’s wrong

3

Good luck with your homework (alone)…

4

This is third SQL statement that retrieves everyone’s name and the GuestNo who have made a reservation at the hotel. I don’t know where to add the count function without getting a Syntax Error.

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo

This is giving me a Syntax error in quert expression GUEST.FirstName, GROUP BY GUEST.GuestNo.

5

Assuming you’re working with MySQL, does GROUP BY need to come before ORDER BY ?
http://dev.mysql.com/doc/refman/5.0/en/select.html

6

I’m working with Access. I don’t think it matters.

7 
SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo

FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo

GROUP BY GUEST.FirstName

I’ve tried this combination as well but Access tells me, You tried to execute a query that does not include the specified expression ‘GuestNo’ as part of an aggregate functio

8

:d’oh’ and it’s right there in the title under my nose.

How about something like
COUNT(RESERVATION.GuestNo) AS GuestReservations …

10

Even with the alias it gave me the same thing.

11

You’re getting all confused here

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo

In this query, look at the fileds chosen in the SELECT clause. You have two of them.

Your GROUP BY should list all the fileds you use in your SELECT… except those who are affected by an aggregated function (like COUNT in this case)

So it should be

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
GROUP BY GUEST.FirstName ORDER BY GUEST.FirstName

And you’re wrong. Just because you’re using ACCESS, how you write your SQL does matter. ACCESS follows ANSI92. But, let’s say that it didn’t… you will not always work with ACCESS, so you should get into good habits anyway.

So now that you know this… Can you explain why the second query doesn’t work? It would be nice that you try to explain it to use so we know that you understood it… HINT: it has nothing to do with the alias

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.