Count Help (1 Viewer)

[deanaldo]

Hi, Having a few problems with a query which I am trying to get a count but its counting against the wrong thing if that makes sense.

I am trying to run a query to show how many customer a salesperson has booked in. All the data is being pulled from one table. I am pulling 3 fields, salespersonID, customerID, bookedDate.

As each booking is recorded individually there are many entries for the salespersonID, I want to show how many bookings a salesperson has made. So I want to count the customerID and show that against the salespersonID i.e salespersonID 2 made 76 bookings over a period of time.

At the moment though I have 76 different lines in the query with salespersonID 2 next to them all.

I am using the bookedDate field so I can specify the date range for the whole query.

I have tried a count on the customerID but it just counts how many customerID's were booked on the bookedDate.

Anyhelp would be appreciated thanks.

 

[The_Doc_Man]

When you want to do a breakdown of how many X are associated with Y, you will generally need some syntax in SQL that resembles Count(X) from .... GROUP BY Y....


In the query design grid, you can get this same effect by clicking the Summation Query icon in the tool bar. It looks like the Greek letter upper-case SIGMA.

Once you do that, include the fields you want to count AND the fields for your break-outs. In this grid for summation queries, you can mark fields as Sum() or Count() or whatever, OR you can mark them as GROUP BY (which is also in the available drop-down).

This might do what you want.

Be aware that if you do this, Access might not show counts for all salesmen - if they have no individual sales records in the original table.

 

[alktrigger]

Here's how I would go about it. I would make 2 queries:

Query 1: qryTempBook (change the names)

SELECT tblBooked.SalesPersonID, tblBooked.CustomerID
FROM tblBooked
WHERE (((tblBooked.BookedDate) [COLOR=Red]Between #8/1/2009# And #8/31/2009#[/COLOR]));

Red - Change this to whatever control you are using.

Query 2: qryTempBook2

SELECT qryTempBook.SalesPersonID, Count(qryTempBook.CustomerID) AS CountOfCustomerID
FROM qryTempBook
GROUP BY qryTempBook.SalesPersonID;

 

[deanaldo]

Thanks for both of your help, got it working now