Count Entires and ignore duplicates

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I have read thru a few of the posts regarding counting records and ignoreing
duplicate entries. Which has left me bewildered.

I have multiple tables that are all linked in a one to many relationship.

The common filed that links all tables together is named MM#.

I have a table that contains the MM# and the name of an agency (AgencyName)
and county of the angecny (Agency County).

These fields (AgencyName and Agency County) contain duplicate information.

MM# may not have a duplicate entry. A specific MM# is assigned to each
record. Each Record may have multiple AgencyName and or Agency County
entries associated with it.

I would like to create a report that will show me how many MM#s are
attributed to each Agency name and each Agency County.

I would also like to be able to prompt the user for a date range. (I.e.
7/1/2004 thru 6/30/05).

I know this may be a multi step process, but I really need some guidence.

Thank you.
 
Try something like:

SELECT [AgencyName], [Agency County], Count([MM#]) AS MyCount
FROM YourTable
WHERE (criteria...)
GROUP BY [AgencyName], [Agency County]
 
Back
Top