How do I limit the records this duplicate rpt returns? 2

[pyroskliq]

I have a table with around 1 million records that is replaced each week (instead of appended to) by a new table with about 45,000 new records. All records are date stamped (so I am able to tell when the records were added to the table).

I am trying to create a query that will return to me duplicate records of only the latest additions.

For instance, this week records with a date stamp of Jan. 12 were added to the table. I have a duplicate query that I'm using, but it will return dups whether or not they are duplicates of the Jan 12 records. If two records from July are duplicates, I don't need them. If a Jan 12 record has a July duplicate, I do need it.

My query is just one I've created using the dup wizard, which is all I really need, except for this one sticky point.

Hope it makes sense to somebody.
Thanks in advance.

Brian

 

[albywalt]

How does this work for you:

Create a new query and add your table to the design view twice. Join on all fields that you want to match on. Let's say you have Table1 and Table1_1 (this is an alias for Table1). You'll need a field such as Expr1: Format([Table1]![DateStamp],&quot;mmyyyy&quot with a criteria of <>Format([Table1_1]![DateStamp],&quot;mmyyyy&quot to give you duplicates from different months. Then you'll need to eliminate duplicates (of the duplicates) so you'll need another criteria such as [Table1]![DateStamp] >[Table1_1]![DateStamp]. You may have to use grouping depending on what you want, but this should give you one record for each duplicate set that occurs in different months.

Jeff

 

[JerryDennison]

Are you looking for duplicates within the same table? If so, you'll need to create a query that returns only those records you want to find the duplicates for (i.e. those within a specific date range). This query needs only the fields containing the duplicate data and the date field to limit the records to(you may want to add parameter(s) that let's the user decide which month/year to find the duplicates for). On another query place the original table and the query you just made. Link the two together using the field(s) that contain the duplicates. Place the fields you want displayed on the QBE grid making sure you also include the date field. You'll need to set the date field criteria to NOT include the dates used for the other query(this will prevent a record from treating itself as a duplicate). Run the query and you should get what you're looking for.

 

[pyroskliq]

Thanks to both of you! A little mix of both ideas gave me exactly what I was looking for.

Brian