show single entries not duplicates (1 Viewer)

radshar

Registered User.
Local time
Today, 04:03
Joined
Aug 5, 2016
Messages
32
Is there a way to create a query which would display only the first occurrence of duplicates entries based on a uniqueID, but would include a count of how many occurrences there are?

I am using MS Access 2013.

thanks,
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:03
Joined
Jan 23, 2006
Messages
15,364
duplicates entries based on a uniqueID
Can you provide an example of this set up?

If you have a table with an ID field that you have set as NoDuplicates, I'm not sure how you arrived at the set up you have.
 

radshar

Registered User.
Local time
Today, 04:03
Joined
Aug 5, 2016
Messages
32
I have an ID which is a regular AUTONUMBER, but I created a separate unique ID called "COMBO" in my table. You can see in the attachment the expression I used to create this unique ID per duplicate.
The duplicates will share 3 fields in common, the expression combines these 3 fields.

Note, my unique ID cannot be a primary key because it has an expression.


Can you provide an example of this set up?

If you have a table with an ID field that you have set as NoDuplicates, I'm not sure how you arrived at the set up you have.
 

Attachments

  • COMBO expression.doc
    45.5 KB · Views: 72

radshar

Registered User.
Local time
Today, 04:03
Joined
Aug 5, 2016
Messages
32
Hmmm this does not really do the trick...
it just keeps prompting me that I have duplicates..
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:03
Joined
Jan 23, 2006
Messages
15,364
You do not need the field COMBO.
You create a unique composite index on the fields
Account
Tradedate and
TradeID
 

radshar

Registered User.
Local time
Today, 04:03
Joined
Aug 5, 2016
Messages
32
Yeah that is what I did.
I had all my fields indexed so I removed the indexes and created one index with the 3 columns. but it does not work.
I'll try again.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:03
Joined
Jan 23, 2006
Messages
15,364
Suggest you post a copy of your database. Remove anything confidential first.

The composite index is marked unique, right?
 

Users who are viewing this thread

Top Bottom