Manipulating the 'Totals' function

[Davrini]

Hey!

I wondering if it is possible at all to manipulate the 'Total' function in a form either through VBA or, preferably, through the query itself.

What I'm trying to create is a form that shows figurative data about attendance at a Clinic. There are a few categories that I want it split up by - Gender and Status (and perhaps, further along the line, Age Ranges and Location, but for now, stick with the former two).

The Average function returns the Average value of Session Attendance for:

Status   Gender
  --       --
Open     Male
Open     Female
Closed   Male
Closed   Female

But what I want to do is to return the values for Both Open, Closed, Male and Female totals. I.e. I want my options to be:

Status   Gender
  --       --
Open
Closed
         Male
         Female
Open     Male
Open     Female
Closed   Male
Closed   Female

I know that in the query, I can have only Status selected, or only Gender Selected, and it shows data for all under that one category, but I want the option to split it in a form. Also want the option so that if neither 'Gender' nor 'Status are filtered, then it shows the total of everything, rather than splitting it.

Possible? Using Access 2010

 

[John Big Booty]

You won't be able to do this with a single query.

I think you'll need to create a number of sub queries to calculate the various totals you are after and then bring those all together in your final query.

 

[Davrini]

Okay, I'm up for that.

So I've just created 4 queries, each with the same fields. How do I combine the four Queries into one - That is, have 9 different rows (4 from Specific, 2 from Gender, 2 from Status, and 1 from Total). If I could combine these onto one query, then I would be able to create my form and report for the Average totals.

How can I combine the 4 queries?

Edit: Figured it out using UNION ALL in SQL. Yey! Thank you very much for your nudging me in the right direction!

 

[Pat Hartman]

Use a union query.
Select * from qry1
Union select * from qry2
Union select * from qry3
...