Results 1 to 2 of 2

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

  1. #1
    oldgent is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    1

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

    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

  2. #2
    apr pillai's Avatar
    apr pillai is offline Contributor
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    134
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problems with totals
    By marksnwv in forum Queries
    Replies: 2
    Last Post: 06-27-2011, 08:44 PM
  2. Problems with creating Multiple Input Masks
    By deiniolj in forum Programming
    Replies: 8
    Last Post: 01-26-2011, 01:44 PM
  3. Replies: 3
    Last Post: 08-29-2010, 05:34 AM
  4. Report control of a field display/no display
    By systems013 in forum Reports
    Replies: 5
    Last Post: 02-01-2010, 07:44 AM
  5. Replies: 0
    Last Post: 12-14-2009, 07:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums