I have a master table (Project List) along with several sub tables that are joined on one common field (RecNum). I need to get totals for all of the sub tables, by column and am not sure how to do it. This is a sample of the table design. There are more columns in each table but I'm showing a sampling of the column names and values to get an idea of what to do.
Project List
| RecNum |
| 6 |
WeekA
| RecNum | UserName | Day1Reg | Day1OT | Day2Reg | Day2OT | Day3Reg | Day3OT |
| 6 | JustMe | 1 | 2 | 3 | 4 | 5 | 6 |
| 6 | NotMe | 1 | 2 | 3 | 4 | 5 | 6 |
WeekB
| RecNum | UserName | Day1Reg | Day1OT | Day2Reg | Day2OT | Day3Reg | Day3OT |
| 6 | JustMe | 7 | 8 | 1 | 2 | 3 | 4 |
| 6 | NotMe | 7 | 8 | 1 | 2 | 3 | 4 |
So the first query should return the complete totals for both users, like this:
| sumDay1Reg | sumDay1OT | sumDay2Reg | sumDay2OT | sumDay3Reg | sumDay3OT |
| 16 | 20 | 8 | 12 | 16 | 20 |
The second query should return the totals for just a specified user, (WHERE UserName = 'JustMe') like this:
| sumDay1Reg | sumDay1OT | sumDay2Reg | sumDay2OT | sumDay3Reg | sumDay3OT |
| 8 | 10 | 4 | 6 | 8 | 10 |
Aucun commentaire:
Enregistrer un commentaire