Acheiving Countifs in a Query

[Ganymede]

This is an offshot of my previous thread. I have a table that has many fields, four of which are relevant here: the unique ID, Issue, Engagement and Year. I want to create a query that shows me, for each row on that table, how many other rows have the same combination of Issue, Engagement and year. In other words, it's baiscally like a field with a countifs statement.



I assume this would require one or more queries that makes use of the totals column. But I can't quite wrap my mind around how to pull it off. Can anyone give me a hint as to how I should structure the query or queries?

Edit: One thing I tried was creating a query with a field that concatonated Issue, Engagement and Year and using "Count" from the total line. But I obviosuly don't know what I'm doing because that returned a result of 1 in each row.

[jojowhite]

use Total query (the one with Sigma sign):

SELECT Issue, Engagement, [Year], Count("1") AS CountOfDups FROM YourTableName  
GROUP BY Issue, Engagement, [Year];

[June7]

This table has duplicates for Issue/Engagement/Year combination?
If combination is unique then count will be 1 for each row.

If there are duplicates, then build the aggregate query.
Now if you want to show all fields from the table, join that query to table on the 3 pairs of fields.

Alternative is a DCount() expression, however, domain aggregate functions can slow performance of query.

Provide sample data and desired result as tables in post or attach file.

[Ganymede]

use Total query (the one with Sigma sign):

SELECT Issue, Engagement, [Year], Count("1") AS CountOfDups FROM YourTableName  
GROUP BY Issue, Engagement, [Year];

Wow, that looks like a step in the right direction. Thank you! I tried thgat and it gave me a table with four columns: Issue, Engagement, Year, and the countofdups. I suppose my question is how do I link that to the unique ID from my table? At the end of the day I basically want to create a query that shows the "countofdups" for each unique ID.

[June7]

See post 3.

Alternatively, build a report and use Sorting & Grouping with aggregate calcs. This allows display of detail data and summary calcs without intermediate queries.

[Ganymede]

See post 3.

Alternatively, build a report and use Sorting & Grouping with aggregate calcs. This allows display of detail data and summary calcs without intermediate queries.

Thank you! This solution worked for me.