How to count in a query

F

frank

I am very new to Access, I want to ask a basic questions.

I have a query that is generated from a table using some criteria. One field
of the query is called "company ID". I want to do a cumulative count for the
frequency of each company ID appears in the field. For example, the field is
like

Compan ID
1
1
3
5
2
5
1

The result should be:
ID/Frequency
1/3
2/1
3/1
5/2

Can anyone give me a clue?
 
A

Al Camp

Frank,
Place CompanyID on a query design grid and sort the CompanyID Ascending.
Now select View/Totals from the menu bar to create a "Totals" query.
Add a calculated column to the query grid called CountCID with this in
the Field line of the next column...
CountCID:CompanyID

Now using the TotalLine on the grid... your query should look like
this...

CompanyID CountCID:CompanyID
Group Count

Run the query and you should get
1 3
2 1
3 1
5 2
 
D

Dean

Create a query that groups.
Your fields are:
Company ID (Group On)
Company ID (Count)

Yes, I used the same field twice in one query.
 
F

frank

Hi Al

sound I am in the right track but it just not completely work out. I try to
follow your instruction and seems the query run endless (i waited for 20 mins
now, I have about 8000 Company ID in the field) Here is my Query Design View:

Field \ Company ID \ CountCID:Company ID
Table \ Query 1999 \Query1999
Total \ Group by \ Count
Sort \ Ascending

Thanks a lot
 
Top