SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Access SQL - Nationalties Report

    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:

    Code:
    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.

    Code:
    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:

    Code:
    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
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's probably better to start by using a right outer join instead of the WHERE version of a join.

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    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.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried again as I'd copied the code from the email digest (mistake)
    still I've got another small issue...

    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ah, yes, sorry, i forgot the stupid frickin ms access parentheses!!!
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect Thanks - those bloody parenthesis are the bane of my life too.

    Well done mate, and thanks again.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •