compare 2 tables, like match/merge process

[lorirobn]

Hi,

I could use a hand with a report I would like to create. I am a (former) COBOL programmer and still not sure how to do certain (many!) things in Access/VBA.

I have a Room in a building. The room contains items, as on a table called RoomItems.

There is a possibility of let's say, 100 items that can be in a room. All the possibilities are on another table, Subcategory.

For a given room, I would like to compare the actual with the possible, and report on what is missing in the room. I would also like to do this report for all rooms.

If I were to write this in COBOL, I would write a batch match/merge program, and compare item/category by item/category.

Any suggestions as to how I would do this in Access/VBA?

Any help much appreciated.
Thanks in advance,
Lori

 

[PHV]

Why not simply follow the unmatched query wizard ?
If you're confident enough with SQL you may write it yourself with LEFT OUTER JOIN and IS NULL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Hi PHV,

You are helping me a lot today.

The unmatched query is a new one for me, and after reading your response, I've been playing around with it. Will it determine an 'unmatch' when records do not actually exist on one of the tables? From looking at it, it seems that it determines an unmatch when there is null values in the matching field?

I should have been more clear. When an item is missing for a room, that means there is actually NOT a row on one of the tables (RoomItems) for this item. That's why I was thinking 'match/merge' (in mainframe terms)... so I could look at one table on the left side and another table on the right side, and compare item by item.

Will an unmatched query do something like that?

Thanks again,
Lori

 

[PHV]

This is PRECISELY the purpose of such query ...

 

[lorirobn]

Thanks, PHV.
I realized that I cannot match using just the two tables I mentioned, because there are no fields in common. When working with these two tables together, I generally have to join with another table to get my information. Question: can I join tblRoomItems (the table on the right side of the unmatched query) with another table, so that I can then match to the table on the left side of the query?
For example, I want to look at all Subcategories (tlkpSubcategory). I want to compare this to the RoomItems table, and see what's missing (that would be the table on the right). However, RoomItems table does not have SubcategoryID. I would join it with tblItems, which would give me the SubcategoryID of an Item, and then perform the unmatched query using that SubcategoryID. Is it possible to do something like this, or does an unmatched query absolutely have to have only 2 tables in it?

Thanks,
Lori

 

[lorirobn]

Hi - I got it working, by creating a separate query and referring to it from the unmatched query.

My unmatched query successfully gives me all items on tlkpSubcategory that are unmatched on tblRoomItems.

Now I would like to take it one step further, by having it display all unmatched items by room. I will continue tinkering but would appreciate any suggestions.

My unmatched query is:

SELECT tlkpItemSubcategory.SubcatID
FROM tlkpItemSubcategory LEFT JOIN qry1 ON tlkpItemSubcategory.SubcatID = qry1.SubcatID
WHERE (qry1.ItemSubcategoryID Is Null);