Need help designing query for following problem. Three tables: T1, T2 and T3. T1 has field F1. T2 has fields F1, F2 and F3. T3 has fields F1 and F4. F1 would be a part number that exists in all three tables. F2 is a color. F3 is the id of the group to which F1 belongs. F4 is the number of units of the part sold. I need a query that will produce the following table, giving me the total number of units sold for every group by color (under F5), and the total units sold by group (under F6).
Example: (please excuse periods which I needed to align)
It's complicated but I did my best to describe the problem. Thanks.
Example: (please excuse periods which I needed to align)
T1........T2....T2....T2.........T3.....T3
F1........F1.....F2....F3.........F1.....F4.............F5.......F6
1..........1.......R......A...........1.......4..............10.....25
2..........2.......R......A...........2.......6..............10......25
3..........3.......Y......A...........3.......7..............15......25
4..........4.......Y......A...........4.......8..............15......25
5..........5.......R......B...........5.......2.............2........ 2
For part number ‘1’, the total number of units sold is ‘4’. Part ‘1’ belongs to group ‘A’ and has color 'R'. The total number of units sold for group ‘A’, color 'R' is 10. I want the query to produce 10 in new field F5 for parts '1' and '2', 15 in field F5 for parts '3' and '4', and 25 in field F7 for parts '1', '2', '3', and '4', etc.F1........F1.....F2....F3.........F1.....F4.............F5.......F6
1..........1.......R......A...........1.......4..............10.....25
2..........2.......R......A...........2.......6..............10......25
3..........3.......Y......A...........3.......7..............15......25
4..........4.......Y......A...........4.......8..............15......25
5..........5.......R......B...........5.......2.............2........ 2
It's complicated but I did my best to describe the problem. Thanks.