Select Records based another table...

  • Thread starter Thread starter brownti via AccessMonster.com
  • Start date Start date
B

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!
 
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.
 
I'm not sure what the last part of your post means...

Jerry said:
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
Click to expand...
[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!
Click to expand...
Click to expand...
 
Back
Top