Establish Relationship 1

[thirty4d]

Hello,

I need to do a price analysis:

I have 2 tables: The first table is a listing of tools purchased from a particular vendor that includes an old prices and new prices. (Approximately 3000 records).

The other table is a Tool Usage for the last 6 mths that has Qty and Invoice purchased from the same vendor. (About 500 records).

I want to pull the QTY from one table and plug it into the corresponding records in another table.

Both table has the same Partnumbers fields and Primary ID.

Unfortunately there are records that exist in one table and not in the other and vise virsa.

I think that explains why I am not getting a match.

If anyone has a suggestion on how I could accomplish this thank you in advance.

 

[JerryDennison]

Create a query that has all three tables, parts, prices, and qty. Make sure there are joins between the parts table primary key and each of the other two tables foreign key. Double click each join line and change it to include all parts records and only those matching in the other table. Make sure no join line gets created between the prices and qty tables. Place the fields you want on the QBE grid. You should now have All parts but only records in the other fields for price and qty. You may also wish to go ahead and place each foreign key from the other two tables and set the criteria of Is Not Null on the same line in each. This will prevent a part from displaying if there is no record in both of the other two tables.