Matching values from 2 tables

[davesmith202]

I have three tables: tblProducts1, tblProducts2 and tblProductSales.

tblProducts1
Code Cost
ABC 20
BVC 35
ABC 30

tblProducts2
Code Cost
ABC 10
BVC 55
ABC 20

tblProductSales
Code Rev
ABC 70
BVC 25
ABC 20
BVC 15
DCC 33

I want to produce a query that looks like this:

Code Rev Cost Profit
ABC 90 80 10
BVC 40 90 -50
DCC 33 0 33

How can I do this?

Thanks,

Jon

 

[jeremie_ingram]

1. Put all three tables into a query.
2. Establish the proper type of join on the code field between them.
3. Use defined fields to summarize the fileds.

Your example has left me a little in the dark on how these items relate. You have ABC in there a few times and I cannot determine the logic of what you are attempting. You have 2 cost tables(?) and a sales table. Do you have 1 item listed multiple times in each cost table? I could see it in the sales table (1 item sold multple times) but not the other way around. Make sure you have the proper table structure before you get too far along and get stuck.