Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Primary Side (2003 SP3)

    I have two tables - Customers and Bookings - and I need to count the number of male/female customers per booking date. For example, there are 16 clients comprising 11 male and 5 female, and 126 bookings. The following SQL statement counts the total number of bookings but how do I just count by customer based on booking date?

    SELECT tblCustomers.Gender, Count(tblCustomers.CustomerID) AS CountOfCustomerID
    FROM tblCustomers INNER JOIN tblBookings ON tblCustomers.CustomerID = tblBookings.CustomerID
    WHERE (((tblBookings.BookingDate) Between #1/1/2007# And #12/31/2007#))
    GROUP BY tblCustomers.Gender;


    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Primary Side (2003 SP3)

    If you want to count the number of customers per booking date, you will have to group on booking date as well as on gender.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Primary Side (2003 SP3)

    Thanks but it didn't give the 16 I was expecting instead I got a list of M/F for each booking date.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Counting Primary Side (2003 SP3)

    If you want "to count the number of male/female customers per booking date" why do you expect to get 16?
    Don't you expect a number of Ms and a number of Fs for each date?
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Primary Side (2003 SP3)

    Oh dear, sorry!! My original post was poorly expressed and asked for two different things. I expect to get 16 because there are 16 unique customers that have had 126 bookings between them during 2007. In reality I want to count only records on the Primary key side but based on filtering on the foreign key side.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Primary Side (2003 SP3)

    I think I have solved it but whether it's the most elegant? ... I need to create a query as per my original SQL, then create another query based on the first but selecting unique values eg names and gender. Then I can create a report on the second query and show totals only.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Counting Primary Side (2003 SP3)

    Working out how to ask the right question is often difficult.

    It seems to me that gender has got nothing to do with your question at all.

    What you want to do is just count the number of distinct customerIDs in tblBookings.

    Ufortunately Access SQL does not support Count distinct, but you can do it in two stages.

    SELECT Distinct CustomerID)
    FROM tblBookings
    WHERE (((BookingDate) Between #1/1/2007# And #12/31/2007#))

    Then a second query to count the number of CustomerIDs in the first query.
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Primary Side (2003 SP3)

    Yes, you do need two queries for this, as John Hutchison has already indicated.

    (It would be possible to combine them using a subquery, but that would be hard to debug, and be less efficient).

Posting Permissions

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