Problems creating a report to display totals of several choices in several categories

[oldgent]

I am in the process of creating an Access 2003 database for a small personal-support charity. This database holds details of their “Clients”.
.
In order to keep the data readable I have opted to use populated list boxes for several selections on the main form. This is so that the Client table is directly readable by unskilled people (if absolutely necessary) without having to interpret the data contained in many related tables. Also Access 2003 must be used as they do not have access to any later version.


.
The list boxes take the form of {Age Range: “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”} where Age range is the field name (Control Source) and the rest are the values in the dropdown list (Row Source).
.
I have similar arrangements for Gender, Ethnicity, Impairments and several others; Eleven in total
.
For statistical reasons I need to count the number of entries for each option in each of the different fields. I have created a number of separate queries to do this, one for each group of options.

Each of these queries takes the form:
SELECT Clients.Age, Count(Clients.Client_ID) AS CountOfClient_ID
FROM Clients
GROUP BY Clients.Age;
.
Each generates an output as: (Please excuse the hyphens they are used a spacers to format the layout, as the editor seems to strip out any repeated spaces or tabs)
.
Age--------CountOfClient_ID
Up to 5 -------17
71+ -----------19
6 - 11 --------17
51 - 70 -------15
26 - 50 -------15
17 - 25-------- 8
12 - 16 -------15
.
I now need to create a report that brings together the outputs of all of these queries. This, ideally, should be a single report as:
.
Category ----Total for Year
.

Gender
Male ---------16
Female -------18

.
Age-Range
Up to 5 ------17
6 – 11 -------17
12 -16 -------15
17 -25 --------8
26 -50------- 15
51 -70 -------15
71+ ----------19
.
Etc.......

My problem is that when I try to create the report I find that all the data on the report must come from a single query or table, obviously my report data is created from several different queries. If I use the Wizard to create the form and try adding two, or more, query fields to the list of fields I get the error message “You have chosen fields from record sources which the wizard can’t connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query.”
.
I have tried creating a single query that brings together the several queries and either get a massive output showing all the possible combinations of the fields chosen or the “You have chosen fields from.......etc” error message.
.
I am sure that I have done this before, but as I retired from full-time I.T. some 10 years ago I’m now quite rusty, not to mention forgetful. So any advice would be very useful. I’m fairly sure that it’s relatively easy, but I’m not able to see it at the moment
.
A secondary problem is how to get the initial queries to display their output in the order that the list is in the list box . That is, in the order : “Up to 5”;”6-11”;”12-16”;”17-25”;”26-50”;”51-70”;”70+”. Not as shown in the query output above, this is in alphabetic order, not the order needed.
.
Any, and all, help is welcome. Thanks

[apr pillai]

This is not an efficient database design. You should have assigned a separate Code for each Age Groups and recorded their Age in a single field rather than creating separate fields for each.

I think what we can do here is to create separate Queries for each Age-groups with a fixed structure for all queries and combine them in a Union Query and take the count.

Before attempting to work with Union Queries read the following article to avoid unexpected problems creeping into the result:

Union Query