Counting items in a column

[ReineM]

Hi..I have a query that returns negative and positive numbers -11 through 11. I want to count the number of 1's, -1's, 2's, -2's, etc. in the column (including 0's).

So if my column was:

0
-3
1
0
5
-3
0

The result I am looking for is the total for each:

0 (3)
-3 (2)
1 (1)
5 (1)

I then need to figure out what percentage of the total each one represents.

Thanks in advance for your help..this one has got me stumped!

Reine

 

[scriverb]

Three queries:

qryPass1:
SELECT "LINK" AS LINK, Sum(1) AS RecordCount
FROM tblValues
GROUP BY "LINK";

qryPass2:
SELECT "LINK" AS LINK, tblValues.ValueField, Count(tblValues.ValueField) AS CountOfValueField
FROM tblValues
GROUP BY "LINK", tblValues.ValueField;

qryPass3:
SELECT qryPass2.ValueField, qryPass2.CountOfValueField, Format(([qryPass2]![CountOfValueField]/[qryPass1]![RecordCount])*100,"00.0&quot AS [PerCent]
FROM qryPass1 INNER JOIN qryPass2 ON qryPass1.LINK = qryPass2.LINK
ORDER BY qryPass2.ValueField;

Name the queries as I have and run qryPass3. You can rename qryPass3 to anything you want but the other two are being referenced in the SQL of the last query so leave them alone.

Good luck.
Bob Scriver

 

[scriverb]

Reine, I forgot to mention that I created a table called tblValues with a single field called ValueField. It is this field that I put your test data into to create these queries. You will have to go through this code to change the table name and field name to match that of your table and field.

Good luck. Bob Scriver

 

[ReineM]

Thank you so much Bob! I see now what I was doing wrong....this will help me with other things as well. I appreciate your time!

Reine