Counting duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TJ - 26 Jul 2006 20:04 GMT

I am new to Access so bare with me.
I have a table with duplicate records. I need to count the distinct values.
I understand 'distinct' will not work in Access. How else can I count my
records without duplication?
Thanks.

Reply to this Message

Elaine - 26 Jul 2006 20:23 GMT

Click the Queries tab and then click New
Add the table with duplicates
Slect the fields that have duplicates -- do not include the Primary Key
field if you have one
Click the Sigma (Totals Query) button in the Tool Bar
Click Run -- you will get a list of unique records

> I am new to Access so bare with me.
> I have a table with duplicate records. I need to count the distinct values.
> I understand 'distinct' will not work in Access. How else can I count my
> records without duplication?
> Thanks.

Reply to this Message

John Spencer - 27 Jul 2006 13:02 GMT

Genericly:

SELECT Count(*)
FROM (SELECT DISTINCT FieldA, FieldB FROM YourTable)

Two query solution.
Build a distinct query and save it. This is not the same as Count Distinct.
Use that in a totals query.

>I am new to Access so bare with me.
> I have a table with duplicate records. I need to count the distinct
> values.
> I understand 'distinct' will not work in Access. How else can I count my
> records without duplication?
> Thanks.

Reply to this Message