Help with Condional Summing Plz 1

[Scattter]

I've been tasked with composting a mash of yearly databases to track volunteer hours into one master. This was going well enough till 2013. One program then decided they want to track hours by three sub-programs, as well as still default to a generic code (the one that had been used prior to 2013) if need be, AND have a total that could be reported with the generic hours recorded in prior years.

My Hours table has RecordID, VolunteerID, Program, Date, and Hours as fields. Program is a text field that uses abbreviations (think TeamA, TeamAsub1, TeamAsub2, TeamAsub3, TeamB, ...). I have reports based off crosstab queries that provide (1) monthly totals by program, and (2) program totals by volunteer (ie, each program is a column). For both of these, I need to have a conditional sum - call it TotalTeamA - that will sum Hours for all four TeamA codes (but sum only those four).

Besides the atrocious and constantly changing design, I've been vexed by picking up Access from long ago and learning a new version - 2010. So now it's maybe a case of I can't see the forest for the trees, but I just can't see a simple approach to get these sums. Please help me with ideas!

 

[MajP]

build a small mapping table
tblTeamMapping

[tt]
OldTeam       NewTeam
Team A        Total Team A
TeamAsub1     Total Team A
TeamAsub2     Total Team A
TeamAsub3     Total Team A
Team B        Team B         
Team C        Team C
OtherTeam     OtherTeam
[/tt]

Build a query that joins your table from your table TeamName to tblTeamMapping OldTeam. Then you can use the NewTeam name in your crosstab query.

 

[Scattter]

MajP - THANKS!

Simple yet elegant! I like this solution. And much easier than all the permutation of Iif()s I had been trying!