Is there a way to link 2 tables by fields that may contain null values?
The 2 solutions I am currently using is to either update the tables with "NoValue" when it is empty or to use an iif statement with Nz(table1.field5,"NoValue")=Nz(table2.field5,"NoVa lue").
Neither solution is working well for my current situation as I have 1 table which is linked so I can not update it and I need to check around 30 contact info fields for changes and that is a very bulky iif statement.
The reservation no is the closest thing to a primary key but it isn't exactly. I am not sure what a foreign key is but I doubt there is one.
The table I am receiving is essentially a data dump that I have no control over. I am essentially trying to figure out the new reservations, the cancelled reservations and the modified reservations for the previous weeks data dump. For this reason I don't have much control over the table design.
The modifications could be something as simple as the last name was Smith and now it is Smiths or it could be a completely new name or address. The reservation no is more of a bucket used to hold names then something signifying a distinct reservation if that makes sense.
I hope this answers your question and thank you again for your responses.
If I read your post correctly, you are trying to compare tblOldVersion with tblNewVersion to find modified rows (tuples). Have you tried using the find duplicates query wizard? This query would give you what has not been modified. Now use this query to compare against the tblOldVersion should provide the difference or what has been modified.
Alternatively, in two new queries/tables concatenate all 30 fields of each tables and then in a third query, join these two on this single field ought to point to those that are not equal.
You also seem to indicate that reservation no acts as a temporary placeholder until reservation is confirmed. If so, if in tblOldVersion it has a temporary placeholder in reservation no, and then in tblNewVersion only reservation no field changes to whatever makes it a confirmed reservation, you will count this row as modified, yes?