Access SQL - Nationalties Report

[SortedSites]

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

[Dr John]

It's probably better to start by using a right outer join instead of the WHERE version of a join.

[SortedSites]

It's probably better to start by using a right outer join instead of the WHERE version of a join.

OK - That might be a good place to start, but I'm still stuck.with taking this forwards.

[r937]

SELECT nat.nationality
     , SUM(IIF(bookings.dateFrom BETWEEN #01 January 2009# 
                                     AND #31 January 2009#
              , 1 , 0)) AS bookings_january
     , SUM(IIF(bookings.dateFrom BETWEEN #01 June 2009# 
                                     AND #30 June 2009#
              , 1 , 0)) AS bookings_june
  FROM ( SELECT nationalities.nationalityid
              , nationalities.nationality
           FROM nationalities
         INNER
           JOIN clients
             ON clients.nationalityid = nationalities.nationalityid
         GROUP
             BY nationalities.nationalityid
              , nationalities.nationality ) AS nat
LEFT OUTER
  JOIN clients
    ON clients.nationalityid = nat.nationalityid
LEFT OUTER
  JOIN bookings
    ON bookings.clientid = clients.clientid
GROUP
    BY nat.nationality

[SortedSites]

R937 - I knew I could depend on you to come up with a far better way of doing things. You operate on a far higher level of SQL than I.

Problem though, I ran your code through my access Query browser and it threw an error.



I click OK and Access wrongly (I think) highlights the first "AS" in your code as being the problem.

This bit of code was beyond my ability to write, so its also beyond my ability to debug. Can you give me some pointers?

[r937]

i edited my code about 10 seconds after i posted it, because it had a couple of missing parentheses (well, to be accurate, it ~didn't~ have them )

please try it again

[SortedSites]

Tried again as I'd copied the code from the email digest (mistake)
still I've got another small issue...

[r937]

ah, yes, sorry, i forgot the stupid frickin ms access parentheses!!!

SELECT nat.nationality
     , SUM(IIF(bookings.dateFrom BETWEEN #01 January 2009# 
                                     AND #31 January 2009#
              , 1 , 0)) AS bookings_january
     , SUM(IIF(bookings.dateFrom BETWEEN #01 June 2009# 
                                     AND #30 June 2009#
              , 1 , 0)) AS bookings_june
  FROM (
       ( SELECT nationalities.nationalityid
              , nationalities.nationality
           FROM nationalities
         INNER
           JOIN clients
             ON clients.nationalityid = nationalities.nationalityid
         GROUP
             BY nationalities.nationalityid
              , nationalities.nationality ) AS nat
LEFT OUTER
  JOIN clients
    ON clients.nationalityid = nat.nationalityid
       )
LEFT OUTER
  JOIN bookings
    ON bookings.clientid = clients.clientid
GROUP
    BY nat.nationality

[SortedSites]

Perfect Thanks - those bloody parenthesis are the bane of my life too.

Well done mate, and thanks again.