Create a report based on multiple queries, totals only, no records

[amaxie]

Hi there,

I want to create an output that would automatically display the results (totals only, no records) of multiple queries - more specifically, these queries all draw from the same table, but have several combinations of multiple combinations of values within different fields (e.g. query 1 - field A = 'a' or 'b' and field b = 'x' or 'y' or 'z'; query 2 - ...etc). Because of the complexity of the queries, a cross-tab or pivot table isn't ideal as I would still need multiple pivots, and then do some manual addition. As it is, I have a few different queries in which I change one of the query fields several times, then take the totals from each run and put them into a separate table...tedious! :)

Please let me know if there is any way to create such a report. I am using Access 2010.

Thank you for your help!

Andrea

[pod]

I am not entirely certain I understand you completely but if it is in the querying itself you need help, here goes nothing:

select 
  sum(a) as tot_a, 
  sum(b) as tot_b, 
  sum(c) as tot_c
from 
(
  select sum(val) as a, 0 as b, 0 as c
  from  table1 where txt = 'a'
union 
  select 0 as a, count(val) as b, 0 as c
  from  table1 where txt = 'b'
union 
  select 0 as a, 0 as b, sum(val) as c
  from  table1 where txt = 'c' 
)
 

...
of course, you will put in your more complicated code where needed.

I'm sure someone else might show better tricks but I use 0 as spacers, and it works just the same

[C CSR]

One possibilty may be to stack your queries providing a field for results from each separate query (e.g., Results = Q5(Q4(Q3(Q2(Q1). It looks like a function in a function, but each set of results are actually separated by commas in the final statement. I've done it before, but let me think about it...

[NeoPa]

Andrea, you need to give a clearer question if you want help with it. You omit even to include what type of totals you're trying to work with. I'm sure if you gave it some thought you could come up with something that makes clearer sense.