Counting records from more than one query

[kit_sune]

Hello!

I'm running into an issue where I'm trying to tie several queries together into a list one running total. I have six queries that pull data from the same table, but that meet specific criteria. What I was trying to accomplish was to have a 7th query count the records in each of the six queries, and return the results as a different value for each. Here's an example:

Queries:
qry_1A
qry_1B
qry_2A
qry_2B
qry_3A
qry_3B

Final product:
qry_totals:
1A | 1B | 2A | 2B | 3A | 3B |
20 | 15 | 33 | 19 | 12 | 6 |

What I tried:
Field: 1ATotal: Count([qry_1A].[valueName])
Total: Expression

And I did this for each field that I wanted the query to return, so:
Field: 1BTotal: Count([qry_1B].[valueName])
Field: 2ATotal: Count([qry_2A].[valueName])
Field: 1BTotal: Count([qry_2B].[valueName])
Field: 3ATotal: Count([qry_3A].[valueName])
Field: 3BTotal: Count([qry_3B].[valueName])

The problem is that I don't get what I expected - the query appears to be totaling all the records counted and applying that value to all the fields, so I get this:

1A | 1B | 2A | 2B | 3A | 3B |
105|105 |105 |105 |105 |105 |


Can anyone point out where I went wrong?

Thank you,

Kit

 

[AlexN]

You should first design the total_queries for each one of your six, and then create a union query to combine all six total_queries.

 

[kit_sune]

I was afraid that I would have to do that. I was hoping to stay away from that option because I have two additional qry_totals that I need to establish. In other words, in the example I've given there are six base queries. In total, there's actually 18 base queries. I'll have to create a 18 "total" queries that count them up separately, and then three union queries to display all the data for each of the data sets.

At the end there's going to be 39 queries - that's a lot of clutter just to get to 3 final products!