By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

duplicate records - based on different fields

P: n/a
Hi All,

This might quite simple process for some but I am finding it really
difficult to do. What is required is not a standard Duplicate query but a
variation on it. For example I have following records:

ID_Number Date Name Amount
100 1/1/02 Andrew $100.00
101 1/1/02 Andrew $100.00
102 2/1/02 Andrew $100.00

What I want to create is a query which will display records where Name and
Amount is the same and where Date is different. Therefore this is not rally
a find duplicates query. Would anyone know if this can be done in a single
step. Until now I have been using a long process of extracting results based
on fields being the same and then linking them back it to identify records
which are of no interest.

Thanks
Radek
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
A GROUP BY query should give you what you need.

1. Create a query into this table.

2. Depress the Total icon on the toolbar. (upper case sigma).
Access adds a Total row to the grid.

3. Drag Name and Amount into the grid.
Access "Group By" in the Total row.

4. Drag ID_Number into the grid.
In the Total row, choose Count.
In the Criteria row, enter:
1
The query now shows the names and dates that have a duplicate, and how many
there are of each dupe.

Sometimes people want to know the numbers of the duplicates. Drag ID_Number
into the grid twice more. In the Total row under the first choose Min, and
under the second choose Max.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Rado" <ra**********@yahoo.com.au> wrote in message
news:3f******@news.comindico.com.au... Hi All,

This might quite simple process for some but I am finding it really
difficult to do. What is required is not a standard Duplicate query but a
variation on it. For example I have following records:

ID_Number Date Name Amount
100 1/1/02 Andrew $100.00
101 1/1/02 Andrew $100.00
102 2/1/02 Andrew $100.00

What I want to create is a query which will display records where Name and
Amount is the same and where Date is different. Therefore this is not rally a find duplicates query. Would anyone know if this can be done in a single
step. Until now I have been using a long process of extracting results based on fields being the same and then linking them back it to identify records
which are of no interest.

Thanks
Radek

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.