DCount Quandry

[dannyocean]

Hello All,

I am trying to use Dcount and I have hit a snag.

This is what I currently have
# NC: DCount("[product_Cat]","Audit_Report__Base","[NC_Score]>0 )

This works fine for one type of product, but I am needing to count the non complies for each product type.

I am needing to get the following
Product #NC
HMO 14
Medicare 50

TIA,

Danny

 

[jrbarnett]

Danny,

How are you using this? Is it in a query, or on a report or form?

John

 

[dannyocean]

In a query. That I will use to source a report

 

[dhookom]

Try something like:
NumOfNC: DCount("[product_Cat]", "Audit_Report__Base", "[NC_Score]>0 AND [Product]='" & [Product] & "'" )

Domain aggregate functions such as DSum() and DCount() are generally slow and can ofter be re-written in either the query or report with syntax that is more efficient. However, if this works for you then use it.



Duane
MS Access MVP

 

[jrbarnett]

Hi,

DHookom was right - if you are using it in a query, it is often a lot faster to use an SQL aggregate function as they are called (Sum/Count/Avg/Min/Max etc).

You can do this by going into query design, go to View -> Totals and in your column with the DCount statement, change it to Count (*) and put a group by on the product name, this will get you the product name and totals. If you go to view SQL it will give you something like:

Select Product_cat, Count (*)
From audit_report_base
Where nc_score > 0
Group By Product_cat

John