Hi
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;
What’s tricky is that the report has to compare two or more periods, ie: Bookings by Nationality in January vs Bookings by Nationality in June. OK no big problem, I can run two queries each with a separate date constraint.
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;
Now lets say in January we have bookings from English, American, Canadian and Swiss nationalities and in June we have bookings from American and French clients. As I’m outputting to a <table> I need to show ZERO values for French clients in January and ZERO values for English Canadian and Swiss clients in June.
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
BOOKINGS
bookingid PK
clientid FK
DateFrom
DateTo
Clients
ClientID PK
NationalityID FK
Nationalities
NationalityID PK
Nationality