Compare two tables with different keys

[pbuethe]

I need to compare multiple fields in one table with corresponding fields in another table. For each field that matches I will increment a weighted score. Then, generate a report showing all the possible matches above a certain total score, showing the record IDs of the possible matches, and the names of the fields that matched. The problem is that I cannot join the two tables since they have different primary keys which do not have a corresponding field in the other table, so I don't think I can use queries. I need to compare every record in one table with every record in the other table. The results would look something like this:

HeaderID NewbornID Fields Score
M*21994 157 MLNAME LNAME DOB NFAC NREC 21

This shows that header table record M*21994 matched Newborn table record 157 in the fields MLNAME, LNAME, DOB, NFAC, and NREC for a total score of 21.

How can I do this in code? Thanks for any help.

 

[gromit]

You don't need to join the tables you can just use the HeaderID as the criteria for the query:

SELECT tblNewborn.MLNAME FROM tblNewborn WHERE tblNewborn.MLNAME = "M*21994"

Are you trying to do pattern matching? Wildcards? Then look at the LIKE clause for a query.

hth,

- g

 

[pbuethe]

Gromit,

Thanks for your response, but I guess I was not clear. "M*21994" is not the MLNAME. It is the HeaderID. All the HeaderIDs have the form "M*" followed by a number. The NewbornIDs are just a number. These are the PKs of the respective tables. I am comparing some of the other fields, such as MLNAME (Mother's last name), DOB (Date of Birth) etc. I want to know every instance where a Header record matches a Newborn record in several fields. Some fields, such as MLNAME and NREC (Newborn Medical Record Number) have more weight than others, such as DOB and Sex. There may be different combinations of fields that would score high enough for the 2 records to be considered a possible match. There may be more than one Header record that is a possible match for one Newborn record and vice versa. The point is to list all the possible matches and then have a human look at it to determine which are really matches. This is to avoid having duplicate records in the database.

 

[gromit]

Thanks for the extra clarification.

If I'm understanding, you have two distinct tables and are comparing certain fields in one against a certain group of fields in another. The attached sample shows one example of how to go about this. Open the form frmCompare and click the button. The VBA code then looks at both tblTable1 and tblTable2 and puts the results in tblzComparison.

HOWEVER, some major caveats: This approach currently matches every record in Table1 against every record in Table2. If you have a 1000 records in Table1 and 5000 in Table2, this results in 5 million pairings, each with multiple comparisons. So I don't know whether this is at all practical.

I think the key is to somehow identify some likely candidates for duplicates and then just run a screen against those. You probably have the best idea of what the "most probable" duplicates look like.

You might look at something like a query to sort MLNAME in one of the table and then do a quick lookup for a match (if it is sorted, then you don't have to run through the entire table). NREC might be another good initial sort key.

Is any of this helpful?

 

[pbuethe]

Thanks for your reply, gromit. However, I ended up not having to do this. We are using a simpler method, and it was assigned to another analyst anyway.