Newbie Question - Match Function (i.e. VLOOKUP in Excel)

[golfnut324]

Newbie here, I barely know how to design a query. I have several tables that have been linked from different Excel workbooks. The tables each have a field called "Item Number" that I want to reference in a query and match across the query so that all fields from all tables return in the same record for that Item Number. A very clumsy way of saying that I want to do the same thing that VLOOKUP does in Excel. I couldn't use Excel because the file is too large.

I would greatly appreciate any help you can give here. Please go slowly, I'm just cracking into this database world.

Thanks,

Craig

 

[namliam]

This is called "Joining" in database terms, just add both tables/sheets to the query design and "Drag" the field from one to the other, a line will appear to show it is joined.

You can then add fields from both tables/sheets to the query and it will show you the desired result.

 

[Ziggy1]

start a new query and add the tables to the design grid. because there are 3 it is best that one table be the primary one. click on the item from the primary one and drag over to the Item on the secondary...do this for both...then double click on the lines until you get the "Join Properties" window...set the join so all records of the Primary table will be returned and only those of the matching secondary table that match will return.....do this for both understanding not to contradict the join from the other table...


Picture the primary in the middle with the other 2 on the left/right joining to it...then add your fields to the grid below...start with a few from each table at first ( just double click a field to add it to the grid

 

[golfnut324]

start a new query and add the tables to the design grid. because there are 3 it is best that one table be the primary one. click on the item from the primary one and drag over to the Item on the secondary...do this for both...then double click on the lines until you get the "Join Properties" window...set the join so all records of the Primary table will be returned and only those of the matching secondary table that match will return.....do this for both understanding not to contradict the join from the other table...


Picture the primary in the middle with the other 2 on the left/right joining to it...then add your fields to the grid below...start with a few from each table at first ( just double click a field to add it to the grid

I'll give it a try and let you know. Thanks!

 

[golfnut324]

I'll give it a try and let you know. Thanks!

WOW! I might get to like the database stuff! Thanks a million.

Craig

 

[Ziggy1]

just make sure you understand the effect, if all 3 tables contain a single record per item then it will work great. If one of them contains multiple records of an item...in the case of lets say an Order Detail ( history) then you will see repeated records when you might not want to. In other cases you may have to join more than one field

Some times it is easier to add one table at a time, then run the query...take note of the record count.