Count Same Field

[Bryan Hughes]

Hello,

How do I design a query that counts different criteria number in a single
field?

I have a number field [CRE] that stores a corresponding number based on
client classification.

I need to make a query that will show the total number of 1, 2, 3 , 4 , 5
etc.

Example:

[CRE] = 1; TotalCount =5
[CRE] = 2; TotalCount = 10
[CRE] = 3; TotalCount =7
[CRE] = 4; TotalCount = 15
[CRE] = 5; TotalCount = 2

How can I make this work? It will go into a report.

-TFTH

-Bryan

 

[John Viescas]

Simple Totals query:

SELECT CRE, Count(CRE)
FROM MyTable
GROUP BY CRE

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[John Vinson]

Hello,

How do I design a query that counts different criteria number in a single
field?

I have a number field [CRE] that stores a corresponding number based on
client classification.

I need to make a query that will show the total number of 1, 2, 3 , 4 , 5
etc.

Use a TOTALS query: create a query based on your table; select the
[CRE] field *twice*.

Change it to a Totals query by clicking the Greek Sigma icon (looks
like a sideways W); change the default Group By on the second instance
of GRE to Count. Base your report on this Query and you should be in
good shape!