Data matching by using queries.

[frankt68]

Hi!


I'm using Access 2010 and would like to do the following thing:

In my database, I have two tables. One table is a list of items [ItemsTable1] and contains the following fields: Group_ID, Item_ID, Item_Name.
The second table is a list of a group of items [GroupTable1] with fields Group_ID and Group_name.

I now got a second (newer) list of items [ItemsTable2], with some new data and some older data from [ItemsTable1] but not all. I'd like to compare both tables [ItemsTable1] and [ItemsTable2], first by Group-ID's and then by Item_ID's. If Group-ID and Item_ID from [ItemsTable2] matches those from [ItemsTable1], I'd like to mark the record in [ItemsTable2] as True and if not as False.

Could I do that with a Query?

I don't have much experience with Access...

 

[MarkK]

You want everything with the same structure in the same table, so you shouldn't have two tables, one each for items1 and items2, you should have one table for all items, and a field in that table for 1, or 2.

 

[plog]

First, I agree with Mark if this is a database whose data will be used in the future. If this is just a one-off project t to do some data analysis, this is what I would do:

Create a query in design view with both tables.
Join them by linking via group and item fields you mentioned.
Left click on both linking lines (1 at a time) and in the dialogs that pop up, select the option that shows all from table2.
Bring all the fields from table2 that you want to see into the bottom area.
Add another, using this logic:

RecordExists: isnull(table2.GroupId)

Run the query, a -1 will appear for records that exist and 0 for those that don't.

Be careful, this could produce duplicates. See how many results the query produces and make sure it matches the record total in table2. If more, duplicates exist in table1 for those Group/item permutations.

 

[frankt68]

Thank you both, MarkK and plog for your answers. The plog answer is exatly what I was looking for.