I am having a problem as to how to approch this. I have 8 tables with different structures serving different purposes. Each table has a field named "Region". I need to track counts and sums for each table in a single report broken out by "Region". My current (tedious) workaround is creating a query for each region using the DCount and DSum function specifying the region there. Each query is a one line item which in theory I would like to have recorded in a single query.
What I'm working with...
Query 1
Region: "SCAL"
A Count: DCount(from tblA where "Region" equals "SCAL")
B Count: DCount(from tblB where "Region" equals "SCAL")...
A Sum: DSum(from tblA where "Region" equals "SCAL")
B Sum: DSum(from tblB where "Region" equals "SCAL")...
returns:
Region***A Count***B Count***A Sum***B Sum
SCAL****68*******191******32351***9451
Query 2
Region: "NCAL"
A Count: DCount(from tblA where "Region" equals "NCAL")
B Count: DCount(from tblB where "Region" equals "NCAL")...
A Sum: DSum(from tblA where "Region" equals "NCAL")
B Sum: DSum(from tblB where "Region" equals "NCAL")...
returns:
Region***A Count***B Count***A Sum***B Sum
NCAL****77*******132******41258***7942
What i would like returned...
Region***A Count***B Count***A Sum***B Sum
SCAL****68*******191******32351***9451
NCAL****77*******132******41258***7942
Please help on how to approach this. Thank you in advance.