Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Thanked 0 Times in 0 Posts

    Setting up percentages in a Query (Access 2k, Win2K)

    This is probably simple and I wish teh search function was active...

    I am going to generate a slew of reports and can easily set up simple queries to count and sum various categories. What I want to do is produce charts very easily and use the percentage of the count for a category as the data set for the bar graphs. I began to import the info to Excel and manually construct percentages and thought 'this is too much work' so went back into Access and cobbled together a query that does, in fact, give me the percentages needed.

    However, every time I open it I get the missing paremeter dialog, which I just OK thru and the data shows as expected. How to get rid of the "missing paremeter" alert? I've done a bit of mucking with the SQL but so far no luck.

    Here's the SQL:

    <font color=red>SELECT ClientInformation.ClientAge, Count(ClientInformation.ClientAge) AS CountOfClientAge, [CountOfClientAge]/123 AS Percentage
    FROM ClientInformation
    GROUP BY ClientInformation.ClientAge, [CountOfClientAge]/123;</font color=red>

    running the Query gives me CountOfClientAge? as a missing paremeter. and OK-ing thru gives me the desired data. also, the '123' is hard-coded but I'd like Access to generate that value as well (it's the total number of rows passed by the query).

    any ideas?

    also, I notice that the native Chart in Access will show those North West East South/1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter 'dummy data when editing but the actual data shows up in Preview (in a report) -- which is somewhat irritating but not fatally so. any hints on why this occurs and if it can be fixed woould also be appreciated.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Setting up percentages in a Query (Access 2k, Win2K)

    You're doing too much at once. You are trying to have the query calculate CountOfClientAge (for which it must traverse all records) AND divide CountOfClientAge by 123. You must split this into two queries, and I would throw in a third as well:

    1. A group by query to calculate counts:

    SELECT ClientAge, Count(*) AS CountOfClientAge
    FROM ClientInformation
    GROUP BY ClientAge;

    Save this as qryCountsPerAge.

    2. A query to calculate the number of records:

    SELECT Count(*) As NumberOfRecords
    FROM ClientInformation;

    This query will return only one record. Save it as qryTotalCount.

    3. A query to calculate percentages, based on the queries created in steps 1 and 2. You don't need to join the queries.

    SELECT qryCountsPerAge.ClientAge, qryCountsPerAge.CountOfClientAge, qryCountsPerAge.CountOfClientAge/qryTotalCount.NumberOfRecords As Percentage
    FROM qryCountsPerAge, qryTotalCount;

    <img src=/w3timages/blueline.gif width=33% height=2>

    The chart engine has always shown N / E / S / W etc. data initially, this was the case in early versions of Access and it is still the same in Access 2002. Try saving the report while in preview mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts