DCOUNT (Access 03 SP2)

Topic

New Reply

I can’t get any form of COUNT or DCOUNT to work on my query. I’ve attached a print screen of the data. I want to be able to reflect the number of times the particular afq appears in the data. Seems simple. Why can’t I get it to work?

Reply | Quote

Replies

As far as I can tell, each afq occurs exactly once. So I suppose I’m missing something, or you haven’t provided all information…

Reply | Quote

I didn’t want to bog the system down. In fact, I don’t think it would fit at all. The query returns over 200,000 rows. I just gave a sample.

Reply | Quote

Does this expression do what you want?

DCount("*","TableName","afq=" & Chr(34) & [afq] & Chr(34))

Replace TableName with the name of the table (or query) your query is based on.

Note: with 200,000 records, this will be very slow.

You could also create a Totals query that has only the field afq twice, the first with Group By as Total option, the second with Count.

Reply | Quote

I’m not getting anywhere fast. What I want to have happen is a new calculated field that shows how many times that AFQ appears. I’ve adjusted the sample to show this. For example, in the 1st one (1.13) that particular AFQ shows up 30 times out of the 200,000 records. In excel, this would be a simple COUNTIF(RANGE,CRITERIA). The problem you know is the 200,000 records. Maybe your DCOUNT formula works, but due to the size, you right, it’s too slow to use. The totals are working, so maybe I’m doing something wrong there. I’m at a loss. Any ideas, Hans? THANKS!!!!!

Reply | Quote

Try the following:

1) Create a query based on your table.
Select View | Totals or click the Totals button on the toolbar.
Add the afq field to the query grid twice.
Don’t add any other fields to the query grid.
Set the Total option for the first column to Group By (this is the default anyway).
Set the Total option for the second column to Count.
Save this query as qryCountAFQ.
If you open the query in datasheet view, it should display the count for each afq in a reasonable amount of time.

2) Create a query based on your table and on qryCountAFQ.
Join them on the afq field.
Add all fields from the table that you need to the query grid.
Add the CountOfafq field from qryCountAFQ to the query grid.
This query should display the count of afq’s.

Reply | Quote

Hans to the rescue as usual. Thanks yet again. I keep forgetting to step back and nest my queries. I try to accomplish everything in one rather than serveral. Thank you – thank you – thank you as always.

Reply | Quote