Need help on comparing tables

[smpayne]

I am working with a system where on the accounting side, each record has an account number, but on the operations side, the records have a record number. I have created a lookup table to match the account number to the record number but I still need to reconcile.

At this point, I have all the records matched in a table that shows current inventory with account and record number. I need to compare that table with an operations table with only record numbers to see if they balance. If there are files in the main table that are not in the operations table, I need access to kick out another table or report listing the exceptions.

Can anyone give me guidance with this. I may be making more complicated than it needs to be and it could just be some simple campare function I am not aware of.

I would be eternally greatful!!

Shawn.

 

[Adrianna]

Why not use the query wizard to find unmatching records.

If you're worried about what to compare then use your intermediate table which contains the links between the two (should not just be a look up, you should have a table to actually create the relationship between the two tables), to simple select query with one table, which will include the matching IDs from the other, then use the "find UNmatching" query to determine which ones are not in the other.

Sounds more confusing then it is. I would suggest creating the relationship first and then just searching for null values from one side or the other.

 

[smpayne]

Doesn't appear to work...

I set the relationship up and ran a query, but it only generated the column header?? How should the query look? Sorry, but am very new with access. The relationship is one to one from my main table to the lookup table, then again from lookup to the secondary table I am comparing. I set the query up with 3 tables and the criteria to 'Is Null' under the secondary table. Does this make sense?

Thank you for your assistance with this, I really appreciate it.

Shawn

 

[Adrianna]

What is the repeat or unrepeating data that you are looking for from the main table to the lookup to the second lookup? It sounds like you have data structure issues too if you're worried about duplicate entries in two levels of lookup tables. You may want to consider latering your table structure before you get to far along. With the improper structure, you will find many more queries, codes, etc will become unusually challenging.

PLease provide a little more information as to why or what your duplicate values or rationale is that way others may assist you.