Unique count in Group

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

Bradley C. Hammerstrom

Access2000

A report is based on qryPBIList with three fields: Defect, FileName and
PlaceName. The report is grouped on Defect. The Detail section shows all the
FileNames and PlaceNames (which can be listed more than once) for each
Defect.

I want a calculated field (txtUnique) in the Group Header, next to Defect,
that returns a count of unique PlaceNames for that Defect.

I tried the following in txtUnique:
=DCount("PlaceName", "qryUnqPlaces")
where qryUnqPlaces has two fields, both from the same query as the Report:

Field: PlaceName
Table: qryPBIList

and,

Field: Defect
Table: qryPBIList
Criteria: [Reports]![rptPBI's].[Detail].Defect

This query seems to work, but the calculated field in the Report returns
#Error.

Any guesses?

Brad H.
 
I would remove the criteria from qryPBIList and use:
=DCount("PlaceName", "qryUnqPlaces","[Defect]=""" & [Defect] & """")
This assumes Defect is a text field.
A more efficient method would be to create a totals query
SELECT Defect, Count(Defect) as CountDefect
FROM qryUnqPlaces
GROUP BY Defect;
This new query will count the number of places per Defect. You could save
this query and add it to the recordsource of your report and join the Defect
fields. Then just add CountDefect to your query grid and use it in the
report.
 
Back
Top