Comparison Problems

[dkmn]

I am having problems getting Access to compare 2 Tables. I need it to compare 2 fields from different tables.

Compare two tables, comparing field 1 in table A TO field 1 in table B


Field1 Field2
1 1
2 2
1 2
1
1
2 1


Want it to find the one that has,
1 2
1
1
2 1
How do I get this to work?
I tried using <>[Table A]![Field1] in the Table B Field 1 column in the query, but it doesn't show all the info.
Any help would be great. Thank you.

 

[Andrew Fergus]

Hi, I think we are going to need a little more information. How have you determined the positional values of the numbers in your example? (i.e. in particular the 4th and 5th lines) Are the two tables linked by another field? This must be the case otherwise, TableA.Field1 with a value of 1 will always link to TableB.Field1 with the same value. Accordingly, you won't be able to find any differences whatsoever, unless you have a value in TableA that is not recorded in TableB. Can you provide the actual variable names that you want to compare (I suspect you might have over-simplified the problem) and the key that links the table? However, all said and done, I suspect you need to add something like "Or Is Null" (without the quotes) to your current code.
Andrew.

 

[dkmn]

I have 2 Tables I am working with at a time.
Table1 has ID,Section, Lot, Space,Alpha Space, Last Name, First Name, MI, Interment Year, and Interment Number.
Table2 has ID,Section, Lot, Space,Alpha Space, Last Name, First Name, MI, Interment Year, and Interment Number.
The ID fields is a concantination of the Section, Lot, Space and Alpha Space. Example looks like this. 19-256-9B
Each table has the same ID setup, the ID is not a Primary Key, there is no primary key anywhere.

What I want the query to do is, check to see if everything in Table1 is the same as everything in Table2.
Table1 comparing to Table2, I put all the fields in the tables into a query, in the order above.
In the Last Name, First Name, MI, Interment Year, and Interment Number columns I put <>[Table1]![Last Name], in the Table2 Last Name critieria spot. I do the same for the other fields. On the Table1 side I put "is null" in the Last Name, First Name, MI, Interment Year, and Interment Number critieria spots.

I have been getting all the records I need to do this but I get extra records also. Let say Table1 has a record with Last Name, First Name, NO MI, Int Year and Int#; and Table2 has a record with the same ID with Last Name, First Name, NO IM, Int Year, and Int#. Since there is no MI info in both tables I dont need it to show up in the query. How do I get it to not show up, because there are 90,000 or so records in each table and there is alot of extra stuff. I hope this helps to figure out my problem.
Thanks again. [/u]

 

[Norie]

Are you trying to find duplicates between the tables?

Then why not create a Find Duplicates query using the wizard.

If you want to find unmatched records then you can use the Find Unmatched query.