How to get percentages and numerical values in the same query

[Ronald Mcdonald]

I have a list of things, where each thing can be classified into 3 different classicifications. I want to count how many classifications of each thing there are in my list as well as see what percentage these classifications make up in my list. Only issue is, I have no idea how to work this into a query. I can get a numerical list just fine, but when it comes to percentages, it seems like rocket science.

Let's say I have a table like this.

Entry, Classification
1, A
2, A
3, B
4, C
5, B
6, B
7, A
8, B

So there are 3 A's, 4 B's, and 1 C. I want to produce a query that counts how many there are, and what percentage they make up. I want to make a query that looks like this:

A....3.....37.5%


B....4......50.0%
C....1......12.5%

Should be pretty simple, no? Yet I can't figure it out. I always get errors. For one thing, I don't even know how to use the "CountOf [Field]" in differnet fields.

[Robeen]

I create a Table6 like your example.

Then I created a query [Query11] to count all the records in the table:

SELECT Count(*) AS TotalEntries
FROM Table6;

Then a second [Query12] like this:

SELECT Table6.Classification, Count(Table6.Classification) AS CountClass, Avg(Query11.TotalEntries) AS TotEntries
FROM Table6, Query11
GROUP BY Table6.Classification;

Then a third Query like this:

SELECT Query12.Classification, Query12.CountClass, Query12.TotEntries, ([CountClass]/[TotEntries])*100 AS PercentEntries
FROM Query12;

See screenshot for final result of the three queries.

Hope this helps!

P.S. - there may be a one-step solution . . .

[Robeen]

This combines steps 1 & 2:

SELECT Table6.Classification, Count(Table6.Classification) AS TotalForClass, Avg((Select Count(*)
From Table6 As A
)) AS TotalRecs
FROM Table6
GROUP BY Table6.Classification;

There is probably a way to get the percentage into this as well . . .

[Ronald Mcdonald]

damn I thought I could just do it in one query. Thanks all for your help though, got it to work

[Robeen]

I think there could be a way to get it all into one query - I'll see if I can get one of the experts to look at this.

[June7]

This will probably be easier with a report where you can take advantage of report Grouping & Sorting and summary calcs in headers/footers instead of trying to do all in query.