Count Duplicate Records

[EliteDesk@aol.com]

I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.

I have
SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#]
HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;

It list all agents sorting by AgentID, with a line for each order.
I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!

 

[Laoujin]

Hey there,

What you need to do is remove the tblCustomer.InspectionDate from your group by and select clause and only keep it in your WHERE clause (not in the having clause)

Then you just need to add a COUNT(*) to your select clause and you're set.

The result should be something like:
SELECT tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#]
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID, COUNT(*) AS AgentOrderNumber

WHERE (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))

GROUP BY tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#]

ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;

 

[EliteDesk@aol.com]

Thank you for taking the time to reply! I'm getting the following error "Syntax error in from clause".

 

[EliteDesk@aol.com]

Finally got it!

SELECT tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], Count(tblCustomers.AgentID) AS CountOfnumtimes
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
WHERE (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
GROUP BY tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#]
ORDER BY tblAgentList.AgencyCity;