Results 1 to 2 of 2
2003-07-04, 15:29 #1
- 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
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).
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.
2003-07-04, 16:07 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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
GROUP BY ClientAge;
Save this as qryCountsPerAge.
2. A query to calculate the number of records:
SELECT Count(*) As NumberOfRecords
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.