Create a Number for Unique Values (1 Viewer)

[TexanInParis]

You still need a "driver" table, but you can reload it each time you need to compare two tables.

First, create the "driver" table with these fields:
ID - AutoNumber
Main
From
To
SOS

Define a Unique key on Main, From, To, and SOS

Now each time you need to compare two tables, do this:

1) Delete all rows in the "driver" table.
2) Create a UNION query on the two tables:
SELECT Main, From, To, SOS
FROM Table1
UNION
SELECT Main, From, To, SOS
FROM Table2;

Note that the UNION will eliminate any duplicates.

3) Run an Append query into the "driver" table using the UNION query above as input. This will create a unique number for all the combinations of the four fields found in the two tables.

4) Add ID Long Integer to the two tables you're comparing.

5) Run an Update query that joins "driver" to the target table joined on the four key fields, and copy ID from the driver to the target. Do this for both target tables.

You now have matching unique IDs in both target tables and can use that to compare them.

 

[t_bard]

Thanks so much for bringing me so far along in this quest. This was more complicated than I anticipated and I am grateful you took the time to help.Your latest steps do have me stumped for the moment, but I will be going over them in the next few days. I do have one further question. I still am unsure how exactly to link the two Tables once I have prepared them with their IDs as you have shown.However, it's very possible once I finish your steps, it will be more obvious.If not, maybe you could take yet another moment to explain.
Otherwise, best of luck and, again, your expertise was so helpful.

 

[TexanInParis]

Do a JOIN on the matching ID values that you have generated, but be aware that this will get you the Cartesian product of all rows with matching IDs and will not be updatable. It will let you see any minor differences in the rows that all have the same combination of Main, From, To, and SOS.

 

[t_bard]

Just a join? Finally something that I can do well.
I wonder if you could clarify your Driver Table instructions. I can manage the UNION SELECT on the 2 Tables. I get the unique rows just fine. I can manage a SINGLE SELECT on 1 Table and APPEND it to the DRIVER Table which also gives me unique rows because of the Unique Key in DRIVER, but I cannot get the UNION SELECT Table to APPEND to the Driver Table. I get an error message.

SQL:
SELECT [SGNH_Hra_Main_Street], [SGNH_Hra_From_Street], [SGNH_Hra_To_Street], [SGNH_Hrap_Sos]
FROM [NEW_TEST_REVERSE_1-11_6-13_w/out_M_1]
UNION SELECT [SGNH_Hra_Main_Street], [SGNH_Hra_From_Street], [SGNH_Hra_To_Street], [SGNH_Hrap_Sos]
FROM [NEW_TEST_REVERSE_6-13_1-11_w/out_M_2];
... works for the 1 Table, but
SQL:
INSERT INTO DRIVER ( SGNH_Hra_Main_Street, SGNH_Hra_From_Street, SGNH_Hra_To_Street, SGNH_Hrap_Sos )
SELECT [SGNH_Hra_Main_Street], [SGNH_Hra_From_Street], [SGNH_Hra_To_Street], [SGNH_Hrap_Sos]
FROM [NEW_TEST_REVERSE_1-11_6-13_w/out_M_1]
UNION SELECT [SGNH_Hra_Main_Street], [SGNH_Hra_From_Street], [SGNH_Hra_To_Street], [SGNH_Hrap_Sos]
FROM [NEW_TEST_REVERSE_6-13_1-11_w/out_M_2];
...gets error Msg "Syntax error in FROM clause"
Also, I may be muddling your term "unique key" because I'm not sure if it was a 4-way primary key or a a 4-way index (I tried both)
Appreciate any advice you can give.

 

[TexanInParis]

Close, but no cigar. You have to save the first UNION query, then use it as the source in your Append query. Let's say you save the first query as qryAllCodes. Your insert looks like:

INSERT INTO DRIVER (SGNH_Hra_Main_Street, SGNH_Hra_From_Street, SGNH_Hra_To_Street, SGNH_Hrap_Sos)
SELECT * FROM qryAllCodes;

 

[t_bard]

Texan,

Thanks for getting me out of that quagmire. I was able to update and the IDs were unique and matching. Very cool. Only thing though, I don't think I explained myself well. As you have guessed I don't speak the language well. When I did a join of the ID fields, the result was Access matching every instance of the ID with every other instance so my two 800 row Tables became an 11,000 row Table. I really need the ID's to only appear once for each unique sign (or row), so an 800 row Table compared with an 810 row Table will only yield 810 rows with one side containing 10 blanks. The tricky part is some of the blanks will be from Table 1 and some from Table 2, but I am still hoping they will share the same ID "vicinity"
Here's hoping this is not a big deal.

 

[TexanInParis]

That's not possible unless you match on other criteria. For any given combination of Main, From, To, and SOS, you have duplicate rows, so linking on just that criteria (whether you use the four fields or the unique ID we generated) will yield a Cartesian product - apparently 11,000+ rows! If you want to find out which combinations do not exist in one of the full data tables, outer join your "unique" table that has all the IDs with one of the full tables and select only the rows where there is no match. Something like:

SELECT Main, From, To, Sos
FROM Driver LEFT JOIN FullData
ON Driver.ID = FullData.ID
WHERE FullData.ID Is Null;

"Driver" is the table with a single row for each ID, and "FullData" is one of the tables with the duplicate rows.

 

[t_bard]

No matter. I'm going to go to town with your Unique ID procedure. I'll give blocks an ID and the sign descriptions IDs (for quicker recognition), and have 2 Tables open side-by-side, when necessary.
Thanks again for all your help.Hope you can stick around and help others as much.