Comparing 2 records on 1 table

[rizd999]

I have a table with multiple fields and I need to compare 2 records with 2 different KeyIDs. For the most part, these 2 selected records should match (except for the KeyID of course) but if a field does not match with the second record's field data , I wanted it to display the difference on a seperate query? view. I was thinking of doing this in VB cycling through all the records (two at a time) and comparing each field but I just wanted to know how you gurus would do it using a SQL query. Thanks in advance!!

 

[Calvin]

I don't know how complex you want your output or criteria but start with the "Find Duplicates Query Wizard" and then expand/modify it with additional criteria as you figure out how it is going to work for you and you understand the language better.

 

[rizd999]

Hmmmm...I tried that. First, it only allowed me to compare 10 fields (this table has more than 10) and Second, I couldn't be specific as to which 2 records I wanted to compare.

 

[Calvin]

Then is sounds like SQL won't do it for you, your going to need a custom function using a recordset and probably an array that loops through the recordset and does field comparisons one record at a time that will ultimatly build/add duplicate data to another table.
Start with opening and reading and navigating Recordsets, you can search this site or Access' help files. Once you figure out how recordsets work, you'll just have to define the logic that qualifies as a duplicate and go about adding the duplicate data to the duplicates table.

 

[o1110010]

... Start with opening and reading and navigating Recordsets, you can search this site...

This is the best thread on this forum that relates to what Calvin is saying:
http://www.access-programmers.co.uk/forums/showthread.php?t=55486&page=1

 

[rizd999]

Really?? Oh well. I've worked with recordsets in VB before and I'm quite comfortable with that already, I just thought there might have been an easier way or less cumbersome way using some nifty SQL procedure to compare the records. Other than looping through fields in a recordset, I'm going to have to loop through tables in an array too (the bigger picture) for other records based on those KeyIDs so cutting down on actual record comparison code would have been optimal.

Thanks for the info though!!

 

[rizd999]

Pat,
Well, I do know the 2 distinct KeyIDs of the records that are going to be compared. So based off a simple query, I was going to make 2 recordset (OriginalRS) that contained only the record(s) of the first KeyID and then (DuplicateRS) that contained only the record(s) of the second KeyID. Once all that is established, initialized, filled, etc.
'Set a loop to loop through the records

'Set a loop to loop through the fields
For Each FLD in OriginalRS.Fields
if OriginalRS(FLD.Name).Value<>DuplicateRS(FLD.Name).Value then
'Put it on some newly created output table or perhaps a text file??
end if
Next FLD
Next Record

Something along those lines....

Hmmm....there is one field that will always be the same between both records but in no way is it distinct with the rest of the table. Unless I created a simple query recordset based on those two KeyID records and then did that join along the lines you were talking about?