Defining what is or what isn't a 'duplicate' record

[pyroskliq]

I have a duplicate query that produces sets of records that are duplicates based on 6 different fields.

My problem is that there is a seventh field that I need to use to limit some of the dups. This seventh field is a record identifier that is unique to the record. This field is not actually a number, but a string. Most of the record identifiers end in a zero, but on occasion can end in 'A'. When this happens, I have a record number '0000012340' and a record '000001234A'.

If two records are duplicates (based on the six fields) and both end in zero (or both end in 'A'), they are duplicates, but if one ends in zero and one ends in 'A', they are not duplicates.

I'm having a hard time figuring out how to put this constraint in my query. Any suggestions?

thanks.

 

[JerryDennison]

Put join lines between every field you want to use as part of the duplicates. So in this case you'd have a join line between all seven fields. Only those records where every field matches will be included.

 

[pyroskliq]

I've already got inner joins on the 6 fields that I need to check, but the 7th 'record identifier' field will never be the same. I just need to identify a record that ends in an 'A' as NOT a duplicate of one that ends in a 0.

 

[RickSpr]

Could you just eliminate all the nnnnnA records from consideration, using a constraint such as "NOT LIKE '*A'"? It seems to me that would work.

To implement this, you could create a query that discards the 'A' records from the original table, then do a Find Duplicates query on the first query. Rick Sprague

 

[pyroskliq]

Yeah, I tried that already as well. What I get then is the original record with a '0' on the end and no record where the one with the 'A' would be.

I've used the "Not Like '*A' on both tables, and I still get a record, which I'll call the 'original' record and instead of the 'A' record I get nothing, resulting in a query with one record.

Results kind of like this without the "Not Like '*A'":

Field1...Field2...Field3...Field6...Record#00000050
Field1...Field2...Field3...Field6...Record#0000005A

Then when I add in the "Not Like '*A'" I get:

Field1...Field2...Field3...Field6...Record#00000050

And thats it...instead of nothing.

 

[pyroskliq]

RickSpr,

Your idea of creating a second table without all the 'A' records would work, but I can't do that. The table contains around 1,100,000 records and is about 1.1 gig in size. It's housed on an sql server an I just link to the table with access.

Still looking for another way around it...

 

[RickSpr]

Huh? I didn't say anything about creating another table. I just said to create a query that eliminates the "A" records, then build a Find Duplicates query over that.

It sounds as if you think either that the first query would build an actual result table, or that you can't build a Find Duplicates query over another query. Both those statements are false, though. Rick Sprague