question about "unique values"

[Phil Hayes]

I know that I can eliminate duplicate rows in the result
set of a query by setting the Unique Values property to
true. What I would like to do is figure out how many
duplicates there would have been for each row of that
result set - not the total number of duplicates, but the
duplicate count by row. I can't figure it out from the
documentation. Can anyone help?

Thanks,

Phil

 

[John Vinson]

I know that I can eliminate duplicate rows in the result
set of a query by setting the Unique Values property to
true. What I would like to do is figure out how many
duplicates there would have been for each row of that
result set - not the total number of duplicates, but the
duplicate count by row. I can't figure it out from the
documentation. Can anyone help?

A Totals query is the ticket here. Turn off the Unique Values property
(it would be belt and suspenders). Create a Query based on the table;
change it to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M, or W, depending on whether you lean to the left or
right).

In the Fields put * in one Field cell and the fields which constitute
duplicates in the rest; change the Total row operator to Count under
the * and leave it Group By for the rest.

 

[Phil Hayes]

John,

Thanks for the tip. It didn't quite work as you
mentioned. It would not let me put * in the field name
and then do a total, saying that * represented all the
fields and so couldn't be totalled. However, when I used
the record id (primary key), it worked as desired. So,
you set me on the right course.

Many thanks,

Phil