Duplicate records

[Daryl]

I’ve developed a query on 3 different tables that generates all the records I need ... and then some (duplicates). The relationships are one-to-many (A to B) and many-to-one (B to C). There is no relationship between tables A and C.

The output fields of my query which are later needed for a report)are:

Field 1: Form_ID (in table A and B)
Field 2: Form_Title (in table A)
Field 3: Eff_Date (in table B)
Field 4: DOI_number (in table B and C)

My problem is to get the query to show only the first instance of the duplicate records and all the records that have no duplicates. Let me know if you need more information.

As always, any help is greatly appreciated.

 

[Rich_Lovina]

I have had this problem also, and I solved it by tagging the duplicate set of records with a yes, assuming you can use your wizard find duplicates.
Then set up a select query to find untagged records.
As to quickly displaying the first instance of a duplicate, well I'm still learning by changing options in the design window of your query.

 

[Daryl]

Thanks for responding. By tagging, do you mean set up a ‘duplicates’ field in a table? If so, I would still have to look at each duplicate (or triplicate, etc.) record and determine which is the first instance of the record. Actually, first instance is incorrect. I’m looking for a way to identify the record with the earliest date. Let’s assume my query returns the following records. Records with the same letter prefix are the same except for the Eff_date (which is shown).

Record A1: 11/18/94
Record A2: 04/16/95
Record A3: 02/12/92
Record A4: 05/16/93

Record B1: 11/12/92
Record B2:... 05/22/92

Record C: 03/07/93

Record Z1: 08/08/91
Record Z2: 06/16/98
Record Z3: 07/28/94
Record Z4: 08/03/91

Record Bb1: 09/12/95
Record Bb2: 10/10/94
Record Bb6: 11/11/96

My query generates over 100 such record combinations.

Is there some way to evaluate the results of the above to return records with no duplicates only those ‘duplicate’ records with the earliest date (Records A3, B2, C, Z4, and Bb2)? Perhaps coding (of which I know very little) may be needed and I probably should post this in the Modules / VBA forum.

 

[lscheer]

This method might work for you:

First, to "eliminate" the "duplicates," create a query with all the fields you need in it. Click the Totals (Sigma) button and select "Group By" for all of them (it should do this automatically). Save it. Create another query using the first query as the record source, again, placing all the fields you need to view in the grid. Again, click the totals button. If you run the query now, you should be able to see that you have only one instance for each record. If you go back to the design view and change the "group by" to "max" for the date field, you should get what you want. One thing I'm not to sure of is how it will sort the dates (they might not sort chronologically, so make sure to check).

Just a note, the records you call "duplicates" aren't really, thats why they're displayed numerous times...there is at least one field value that differs from other records (in your case, date).

using nested queries is one way to deal with this.

Good luck.

 

[Daryl]

Thanks for the suggestions but, unfortunately, I get the same results (162 records when the result should be 120 as determinded by a crosstab query). My son suggests that I do an If loop on the Form ID field. Something like if Form ID equals previus Form ID, skip to next record. I haven't a clue how to code that. Any ideas?