Creating SQL statements in Access Databases
-
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
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.
-
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.
-
No data is retrieved back. It's blank.
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.
-
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.
It worked. I was trying to incorporate something else into the statement.
-
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
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.
-
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
SELECT GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST
INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY GUEST.FirstNameThis 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.
-
SELECT GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST
INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY GUEST.FirstNameThis 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.
You need to COUNT(reservation.guestNo) while GROUPing BY Guest.GuestNo
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
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
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 -
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
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.GuestNoThis is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo.
-
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.HotelNoAccess is giving me that HotelNo could refer to more than one table in this SQL statement.
-
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.GuestNoThis is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo.
SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo GROUP BY GUEST.FirstNameI'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
-
Access is giving me that HotelNo could refer to more than one table in this SQL statement.
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
