Select Records based another table...

[brownti via AccessMonster.com]

This may be very elementary, but i can't wrap my brain around it. I have
tried to dummy it down as much as i can for myself, with plans of adapting it
into something working. I have three tables that look like the following:

Tbl 1:
ProdID A B C
1 $1 $2 $3
2 $4 $5 $6
3 $7 $8 $9

Tbl 2:
RmNum ProdID
1 3
1 2
2 1
2 2

Tbl 3:
RmNum Style
1 A
2 C

Relationships are:
[tbl1 ProdID] ------ [tbl2 ProdID]
[tbl2 RmNum] ------ [tbl3 RmNum]

What i want to do is create a query, and a report off of that query, that
gives me the price from Tbl1 "column" based on the Style selection in Tbl3.
So since there is product 3 and 2 in RmNum 1 (from Tbl 2) and style A for
RmNum 1 in Tbl 3, the total would be $11. The same situation for RmNum 2
total would be $9. I hope this makes sense and someone can at least point me
in the right direction. Thanks!

 

[Jerry Whittle]

Your tbl 1 isn't set up properly. It probably should look like so:
ProdID Style Cost
1 A $1.00
1 C $3.00
1 B $2.00
2 A $4.00
2 B $5.00
2 C $6.00
3 A $7.00
3 B $8.00
3 C $9.00

Then the following query will work:

SELECT [Tbl 3].Style,
Sum([Tbl 1].Cost) AS SumOfCost
FROM ([Tbl 2] INNER JOIN [Tbl 1] ON [Tbl 2].ProdID = [Tbl 1].ProdID)
INNER JOIN [Tbl 3] ON ([Tbl 3].Style = [Tbl 1].Style)
AND ([Tbl 2].RmNum = [Tbl 3].RmNum)
GROUP BY [Tbl 3].Style;

I must say that the joins are somewhat strange so you may need more data
normalization to set up your tables correctly.

 

[brownti via AccessMonster.com]

I'm not sure what the last part of your post means...

Your tbl 1 isn't set up properly. It probably should look like so:
ProdID Style Cost
1 A $1.00
1 C $3.00
1 B $2.00
2 A $4.00
2 B $5.00
2 C $6.00
3 A $7.00
3 B $8.00
3 C $9.00

Then the following query will work:

SELECT [Tbl 3].Style,
Sum([Tbl 1].Cost) AS SumOfCost
FROM ([Tbl 2] INNER JOIN [Tbl 1] ON [Tbl 2].ProdID = [Tbl 1].ProdID)
INNER JOIN [Tbl 3] ON ([Tbl 3].Style = [Tbl 1].Style)
AND ([Tbl 2].RmNum = [Tbl 3].RmNum)
GROUP BY [Tbl 3].Style;

I must say that the joins are somewhat strange so you may need more data
normalization to set up your tables correctly.

This may be very elementary, but i can't wrap my brain around it. I have
tried to dummy it down as much as i can for myself, with plans of adapting it

[quoted text clipped - 28 lines]

total would be $9. I hope this makes sense and someone can at least point me
in the right direction. Thanks!