Hello.

I have a form with two buttons.
When the user clicks on the first button, I import two text files into two separate MS Access 2007 tables. They both have the same structure and have a Primary Key "ID" that is auto generated.

The text files fields are delimited by an "*" and there are a maximum of 19 fields per row, although most rows have less fields than that. The field names are Field1, Field2....Field19.

When the user then clicks on the second button, I need to compare these two tables, "Table Old" and "Table New". I need to know if "Table Old" has unmatched records in "Table New" and visa versa - rows in "Table New" that aren't in "Table Old".

I also need to know if any specific field in a matching row is different in "Table New" when compared to "Table" Old field.

My BIG problem is that there is no unique key that I can match on.

Here's an example of some rows in "Table Old" (ANSI X12 837 format).
ISA*00* *00* *ZZ*981234567 *ZZ*921234567 *180109*1512*^*00501*180109151*1*P*:~
GS*HC*982345678*924567890*20180109*1512*180109151* X*005010I20~
ST*837*18*005010I20~
BHT*0080*00*2018010915122527*20180109*1512*RP~
NM1*41*2*112233445*****46*983456701~
NM1*40*2*556677889*****46*923456702~
HL*1**20*1~
DTP*582*RD8*20180104-20180109~
NM1*CA*2*MARKETING BROS ENTERTAINMENT IN*****EI*112233445~

Here's an example of some rows in "Table New" (ANSI X12 837 format).
ISA*00* *00* *ZZ*981234567 *ZZ*921234567 *180110*1033*^*00501*180110103*1*P*:~
GS*HC*982345678*924567890*20180110*1033*180110103* X*005010I20~
ST*837*18*005010I20~
BHT*0080*00*2018011010330717*20180110*1033*RP~
NM1*42*2*112233445*****46*983456701~
N3*1234 RECONSIDERATION LANE~
N4*TUSTIN*CA*92688~
NM1*40*2*556677889*****46*923456702~
HL*1**20*1~
DTP*582*RD8*20180104-20180110~
NM1*CA*2*TRUMP BROS ENTERTAINMENT IN*****EI*112233445~

"Table New" has a couple of new rows.
I created a composite key on both tables using Field2 (e.g. "ISA") and Field3 (e.g. "00").

I first thought to create two record sets, one for each table, start comparing
row one to row one, etc., and when find a row that doesn't match, in either table, write that row to a new table "New Rows" or "Deleted Rows", depending on which record set it occurred in.

Definitely not very efficient!!

I then found some code on the Internet using the Union statement but have not been able to figure out how to get it to work.
(Here's a link to this code:
http://weblogs.sqlteam.com/jeffs/arc...1/10/2737.aspx)

SELECT TableName, ID, Field1, Field2, Field3
FROM
(
SELECT 'BillsOld' as TableName, O.ID, O.Field1, O.Field2, O.Field3
FROM BillsOld O
UNION ALL
SELECT 'BillsNew' as TableName, N.ID, N.Field1, N.Field2, N.Field3
FROM BillsNew N
) tmp
GROUP BY O.TableName, O.ID, O.Field1, O.Field2, O.Field3
HAVING COUNT(*) = 1
ORDER BY O.ID;

The problem is that at some point the Primary Key ID will not match because there may be less rows in "Table Old" or more rows in "Table New".
However, I'd like to have the ID in the results as it will help the user to identify the row where there are changes.
The "GROUP BY" requires me to use all the fields in the SELECT statement.

BTW, I just tried this code to see if the UNION is working per the article, but it returned all the rows from "Table New" and "Table Old" - which I used the same input file to populate.
SELECT tmp.TableName, tmp.ID
FROM
(SELECT 'BillsOld' as TableName, O.ID
FROM BillsOld O
UNION ALL
SELECT 'BillsNew' as TableName, N.ID
FROM BillsNew N ) AS tmp
GROUP BY tmp.TableName, tmp.ID
HAVING (Count(*)=1)
ORDER BY tmp.ID;

I just realized that all the rows are returned because the 'TableName' value is different!

How else could I know which table the row was coming from?!

Any suggestions will be greatly appreciated!

Thanks,
Rita