By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Jan 16 '12 #1
Share this Question
Share on Google+
3 Replies


pod
100+
P: 298
pod
I am not entirely certain I understand you completely but if it is in the querying itself you need help, here goes nothing:
Expand|Select|Wrap|Line Numbers
  1. select 
  2.   sum(a) as tot_a, 
  3.   sum(b) as tot_b, 
  4.   sum(c) as tot_c
  5. from 
  6. (
  7.   select sum(val) as a, 0 as b, 0 as c
  8.   from  table1 where txt = 'a'
  9. union 
  10.   select 0 as a, count(val) as b, 0 as c
  11.   from  table1 where txt = 'b'
  12. union 
  13.   select 0 as a, 0 as b, sum(val) as c
  14.   from  table1 where txt = 'c' 
  15. )
  16.  
...
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
Jan 16 '12 #2

100+
P: 144
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...
Jan 16 '12 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jan 16 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.