# Thread: Counting Primary Side (2003 SP3)

1. ## 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. ## 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. ## 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. ## 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?

5. ## 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. ## 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. ## 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.

8. ## 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
•