Finding and linking unique items

[AcSteve]

Hello,

I am looking for help on how to use code to match certain items in two tables. But I need to track which item matches the item in the other table.

For example I have TableOne that has fields; TableOne_ID(AutoNumber), Item(txt), and TableTwo_ID(Integer).

TableTwo has TableTwo_ID(AutoNumber), Item(txt), and TableOne_ID(integer).

If I populate TableOne.Item just using a letter: lets say I have two records with A, three records with B, and one record with C.

Then in TableTwo.Item I have three records with A, four records with B, and no C's.

If the code starts with TableOne and gets the first A, then goes over to TableTwo --- when it finds the first A there, I need to place the ID from TableOne in TableTwo and also put the ID from TableTwo over in TableOne. etc.

In the end, I need to now which items matched, what their IDs are and the balance of which ones in TableOne could not find a match in TableTwo and vice versa.

Thanks for the assist.

 

[The_Doc_Man]

OK, here is the simple situation. You can't do what you said you wanted to do with the data you described. You need one other table to stand between them because the third field (in both tables) must be single-valued. If you add a record to table B that records a match in table A, you just added another record that table A could match against records in table B. This is a never-ending cycle.

The solution is called a junction table, which you can probably look up in this forum using the Search function in the ribbon just above the top post entry.

Basically, a junction table can be used to track the fact that "record 1 in table A matched record 17 in table B" or something like that. Then you can COUNT the records in the junction table for the various records. For the two tables you named, your junction table COULD be as simple as two LONGs in which you store the TableOne_ID and TableTwo_ID fields. If you add to the junction table, you don't change the two tables that you are counting.

This is an example of the Heisenberg Principle. If you are not careful, the act of observation affects the experiment. This is why you need the third table - to avoid perturbing the first two tables.

 

[AcSteve]

Thanks Doc Man... I'll do the homework on the junction table but even with a table that shows the link, how would you step through TableTwo ensuring I don't recount the first match on item A. Somehow I have to mark TableTwo so I can skip over that item and look for another match on the next item A.. right?

 

[The_Doc_Man]

I don't think so. You can add records to the junction table pointing the other way by adding one more field to that table - "direction." Then you can pass through table 1 to find and store all links with direction 1, and pass through table 2 to find and store all links with direction 2.

I have to say also that your problem probably could be done with a couple of queries based on one-to-many relationships where you didn't enforce relational integrity. You would HAVE to do that because you said you are going to have some zero-match cases.

 

[AcSteve]

Success!...Not that I used a junction table but at least you got me thinking.

I first made a distinct recordset of each item in TableOne. I opened another recordset of TableOne as SQL query filtered on the first item in the distinct list and got the record count. The record count gave me the upper limit to step through TableTwo. TableTwo was also opened as SQL query like TableOne (filtered on the same item). As I step through each item in TableTwo Recordset, I can now take the ID from TableOne, write it in TableTwo, and also write the TableTwo ID in TableOne. Now each table shows which records have a match in the other table and what the record number is.

Thanks for the help!

Cheers

 

[The_Doc_Man]

No problem, Steve. We all go through the situation of needing a little nudge in the right direction sometimes.

Believe me: Been there, done that, bought the T-shirt, and wore the damned thing out from needing so many nudges in the right direction.