Problem in creating a report that displays totals of several choices for several cate

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

Any offers? I really am stuck with this problem.

If necessary I can start from scratch again and try a different structure, but hope that I don't need to.

I would have thought that this sort of regular analysis of the different options in several categories would have been common-place and fairly easy to do, but I just can't see how at the moment.

Have you tried using sub reports?

BTW- are you actually storing the age ranges like "Up to 5", "6-11", etc. or are you storing the birth date and calculating the age ranges (hopefully the latter)

Beetle.

Thanks for the advice, I had just started thinking along those line, along with other possibilities. I'm not terribly familiar with sub-reports, prersumably similar to sub-forms. I shall have to carry out some (more) research.

The "Age Ranges" are actually held as I listed them. For various reasons the charity treats confidentiality VERY seriously. It operates in a similar way to Alcoholics Anonymous and only a very few advisors have access to the full record of each client. The personal identifying details, such as full name, address, telephone numbers etc, are to be held in a separate table, which is linked to more general case details table by a unique Client-ID number. Top level advisors, ONLY, will have access to both tables, Others will only see the Client ID and the case details.

So, most advisors that the client comes in contact with will only know them by a simple first-name, not neccesarily their real first name. Also any other info that could possibly identify individuals, such as full date of birth, is eliminated unless it is vital. As a result of this of these measures a client is only know by very vague parameters, e.g. Jim from Anytown aged between 26 and 50.

Thanks, again, for the advice, I'll look into the idea.

You might want to consider having a lookup table for the Age Range with records like;

RangeID----AgeRange
1-----------Up to 5
2-----------6 to 11
etc.

Then have the users just select the appropriate Age Range from a combo box, storing just the RangeID in any related tables. Then you can sort by the RangeID when needed.