Creating SQL statements in Access Databases

[Richard Andrew x64]

Let's see what your query looks like for the rooms that have been reserved. I suspect that you simply need to negate the criterion for choosing those that have been reserved.

The difficult we do right away... ...the impossible takes slightly longer.

[User 11474011]

SELECT RoomNo
FROM RESERVATION
ORDER BY HotelNo

[Richard Andrew x64]

You could try a subquery:

SELECT HotelNo, RoomNo FROM Room WHERE RoomNo NOT IN (SELECT RoomNo FROM Reservation) ORDER BY HotelNo, RoomNo

The difficult we do right away... ...the impossible takes slightly longer.

[User 11474011]

No data is retrieved back. It's blank.

[Richard Andrew x64]

My SQL may be a bit rusty. But I don't know what your data looks like either. Let's see if anyone else has an idea.

The difficult we do right away... ...the impossible takes slightly longer.

[User 11474011]

It worked. I was trying to incorporate something else into the statement.

[User 11474011]

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. My two related tables are Guest and Reservation.I can't have the FK GuestNo be one of the attributes. This is what I have so far.

Select FirstName, LastName, GuestNo FROM GUEST INNER JOIN RESERVATION ON GUEST.firstname = RESERVATION.guestno

I'm not sure about GuestNo.

[User 11474011]

SELECT GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST
INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY GUEST.FirstName

This is my SQL statement that retrieves all the names of the people who made reservations with the GuestNo appearing as well. I don't know where to incorporate the Count function.

[Chris Quinn]

You need to COUNT(reservation.guestNo) while GROUPing BY Guest.GuestNo

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

[Abdulnazark]

SELECT HotelNo, RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo

[User 11474011]

This is second 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 my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo.

[User 11474011]

Access is giving me that HotelNo could refer to more than one table in this SQL statement.

[User 11474011]

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

[Abdulnazark]

try now I forgot to add table name

SELECT ROOM.HotelNo, ROOM.RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo