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.

 

[Pat Hartman]

Open the query in QBE design view. Right click on the grey background and select properties. Change the DistinctValues property to yes.

 

[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?

 

[Pat Hartman]

The problem is that you are including a column in the query that contains a value that cannot be summarized away. Your problem is exacerbated because your table contains a column that contains multiple pieces of data. If you need to handle the prefix separately from the numeric portion of a field, clearly the data should be stored in two fields, NOT ONE.

You need to break the query into two. The "inner" query will find the earliest date for the prefix. Then use an "outer" query that joins the "inner" query back to the original table to pick up the numeric portion of the mushed field. Unfortunately, you can't use the QBE grid to construct this join since it is on only part of a field. You'll need a function that extracts the prefix. You can use the function in the join as follows -

...
From YourTable As T Inner Join YourQuery As Q on YourFunction(T.YourField) = YourFunction(Q.YourField)