Only include unique occurances

[filla_dilla]

I have a table that tracks the lifecycle of documents. Because one document can have multiple things done to it, the "key" I'm using is repeated each time an action is taken on the document. What I'm trying to accomplish is to get a count of the different document types. The document should only be counted once if the "key" is a repeat. For example, my table looks like:

key date document
1 7/18/11 word document
1 7/18/11 word document
1 7/18/11 word document
2 7/18/11 word document
2 7/18/11 word document
3 7/18/11 Excel
3 7/18/11 Excel

In this example, the results I'm trying to obtain are:

Count document type
2 word document
1 Excel

[Bob Fitz]

Hi

You may like to try this as the SQL for your query:

 
SELECT Count(Temp.document) AS CountOfdocument, Temp.document
FROM (SELECT YourTableName.document, YourTableName.key FROM YourTableName GROUP BY YourTableName.document, YourTableName.key) as Temp
GROUP BY Temp.document;

You will need to enter your own table name where I have YourTableName

[NTC]

An aggregate query, with just grouping - will result with:

1 7/18/11 word document
2 7/18/11 word document
3 7/18/11 Excel

call this query Agg1


then a second aggregate query, using Agg1 for its source - set up to count the document type will result in:

2 word document
1 Excel

Aggregate queries are found in the icon greek E epsilon in the toolbar/ribbon of query design - refer to your Access text book for further instruction detail.

hope this helps.

[Bob Fitz]

Also, it would be better to aviod using "date" as the name of a field because "Date" is a reserved word in access.

[Bob Fitz]

Hi NTC

I know that the solutions we both offered work, but in your opinion, does either have any merits over the other?

[filla_dilla]

Hi

You may like to try this as the SQL for your query:

 
SELECT Count(Temp.document) AS CountOfdocument, Temp.document
FROM (SELECT YourTableName.document, YourTableName.key FROM YourTableName GROUP BY YourTableName.document, YourTableName.key) as Temp
GROUP BY Temp.document;

You will need to enter your own table name where I have YourTableName

This works great! Thank you, Bob. I didn't ask about it, because I thought I could add it without any problem, but I need to prompt the user for a date range. The actual column name is DATERECIEVED, but I wanted to keep the example simple. Following the original example, this is what I'm adding, but it's not working how it needs to:

where date between [Start date:] and [End date:]

[Bob Fitz]

Hi

I think the criteria needs to be in the subquery so
maybe this is what you need:

 
SELECT Count(Temp.document) AS CountOfdocument, Temp.document
FROM (SELECT YourTableName.document, YourTableName.key FROM YourTableName WHERE (((YourTableName.date) Between [Start Date:] And [End Date:])) GROUP BY YourTableName.document, YourTableName.key) AS Temp
GROUP BY Temp.document;

[filla_dilla]

Hi

I think the criteria needs to be in the subquery so
maybe this is what you need:

 
SELECT Count(Temp.document) AS CountOfdocument, Temp.document
FROM (SELECT YourTableName.document, YourTableName.key FROM YourTableName WHERE (((YourTableName.date) Between [Start Date:] And [End Date:])) GROUP BY YourTableName.document, YourTableName.key) AS Temp
GROUP BY Temp.document;

Thank you so much for this, Bob. I've been trying to figure this out for about a week now!

[Bob Fitz]

You're welcome. Glad we have been able to help.