Results 1 to 9 of 9
Thread: Access SQL - Nationalties Report
Jan 28, 2010, 05:41 #1
- Join Date
- Oct 2003
- Tenerife, Spain / UK
- 0 Post(s)
- 0 Thread(s)
Access SQL - Nationalties Report
I have a tricky Access SQL problem which I'll try to present as simply as possible.
I need a count of the number of bookings placed with a hotel according to the clients nationality. Now this is straight-forward:
SELECT Count(Bookings.BookingID) AS nobookings, nationalities.nationality FROM Bookings, Clients, nationalities WHERE Bookings.ClientID=clients.clientid And Clients.nationalityid=nationalities.nationalityid GROUP BY nationalities.nationality;
SELECT Count(Bookings.BookingID) AS nobookings, nationalities.nationality FROM Bookings, Clients, nationalities WHERE Bookings.ClientID=clients.clientid And Clients.nationalityid=nationalities.nationalityid AND bookings.dateFrom between #01 January 2009# AND #31 January 2009# GROUP BY nationalities.nationality;
So question (1) How would I modify the above query to give me counts of ZERO for nationalities that haven't booked?
To further complicate this, the nationalities table contains an exhaustive list of possible nationalities. Many of which have never yet placed a booking with the hotel, so this leads me to question (2). How would I modify the query to give ZERO values for nationalities, only for nationalities that have actually placed a booking at some time with the hotel?
Or put it with an example:
I want to see a Count of ZERO next to "German", only if the hotel has previously had a booking from a German client. And as no Brazilians have placed a booking yet, I don't want them cluttering the output.
In anticipation I wrote a stored procedure called "ACTUALNATIONALITIES" which gives me a list of nationalityId's that actually appear in the Clients table:
SELECT nationalities.nationalityid FROM clients, nationalities WHERE clients.nationalityid=nationalities.nationalityid GROUP BY nationalities.nationalityid;
This is as far as I've got, I need some help putting this all together.
Simplified Data Structure