Multiple Unrelated Tables w/Similar Field in Single Query

  • Thread starter Thread starter Liv
  • Start date Start date
L

Liv

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.
 
I can not figure what you are summing but a union query and then a crosstab
should do it for you.
SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS MySum, "A" AS
MyTable
FROM TableA
GROUP BY Region
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "B" AS MyTable
FROM TableB
GROUP BY Region
....
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "H" AS MyTable
FROM TableH
GROUP BY Region;
 
Unfortunately the Count and Sum functions will not work because I am pulling
counts and sums of data from multiple tables. This is the reason for the use
of domain functions. I am not able to create a crosstab query calling on
"Region" alone because of this as well. i.e. [tblA].Region and [tblB].Region
are two different babies. I am summing a different dollar value field for
each table and counting a different other field depending on the table,
however I'm not sure how that is important. I have two queries, soon to be 7
single-line queries based on the seven regions, and I would like to have one
query, if possible, instead of 7 that are linked. I would not be able to use
this example.

KARL DEWEY said:
I can not figure what you are summing but a union query and then a crosstab
should do it for you.
SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS MySum, "A" AS
MyTable
FROM TableA
GROUP BY Region
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "B" AS MyTable
FROM TableB
GROUP BY Region
...
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "H" AS MyTable
FROM TableH
GROUP BY Region;



Liv said:
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.
Click to expand...
Click to expand...
 
I think Karl Dewey's suggestion should work for you. What I understand you to
say is that the names of your fields vary from table to table, but the values
in the tables for region remain the same.

SELECT Region, Count(Region), Sum(XXX) as TheSum, "A" as TheSource
FROM TableA
GROUP BY Region, "A"
UNION
SELECT Rgn, Count(Rgn), Sum(Yyy), "B" as TheSource
FROM TableB
Group by Rgn, "B"
UNION
SELECT Section, Count(Section), Sum(DollarAmount), "B" as TheSource
FROM TableC
Group by Rgn, "C"

If I have misunderstood, perhaps you can share two or three of the query
strings you are using now. (View: SQL from the menu, copy and paste)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
It took me a wknd of mind-rest but I got it! Thank you. I have another
question. If I wanted to put certain "Regions" in a group, for instance
showing NCAL data, SCAL data, and California data(NCAL and SCAL combined)

KARL DEWEY said:
I can not figure what you are summing but a union query and then a crosstab
should do it for you.
SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS MySum, "A" AS
MyTable
FROM TableA
GROUP BY Region
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "B" AS MyTable
FROM TableB
GROUP BY Region
...
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "H" AS MyTable
FROM TableH
GROUP BY Region;



Liv said:
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.
Click to expand...
Click to expand...
 
Something like this with the union query named QryMyRegion --
SELECT SUM(IIF([Region] = "NCAL", [MyCount],0)) AS NCAL_Count,
Sum(IIF([Region] = "NCAL", [MySum], 0)) AS NCAL_Sum, SUM(IIF([Region] =
"SCAL", [MyCount],0)) AS SCAL_Count, Sum(IIF([Region] = "SCAL", [MySum], 0))
AS SCAL_Sum, SUM(IIF([Region] = "SCAL" OR [Region] = "NCAL", [MyCount],0))
AS California_Count, Sum(IIF([Region] = "SCAL" OR [Region] = "NCAL",
[MySum], 0)) AS California_Sum
FROM QryMyRegion
WHERE (Region = "NCAL" OR Region = "SCAL");




Liv said:
It took me a wknd of mind-rest but I got it! Thank you. I have another
question. If I wanted to put certain "Regions" in a group, for instance
showing NCAL data, SCAL data, and California data(NCAL and SCAL combined)

KARL DEWEY said:
I can not figure what you are summing but a union query and then a crosstab
should do it for you.
SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS MySum, "A" AS
MyTable
FROM TableA
GROUP BY Region
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "B" AS MyTable
FROM TableB
GROUP BY Region
...
UNION ALL SELECT Region, Count([Region]) AS MyCount, Sum([SomeField]) AS
MySum, "H" AS MyTable
FROM TableH
GROUP BY Region;



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