Getting distinct count from a column in a query

[Terpsfan]

I am using Access 2000. I am trying to find the distinct count of the description column within the query. In Access 2002, I could create a subquery SELECT Count(Distinct Description)... Access 2000 doesn't allow this. Any ideas? Thanks

PARAMETERS [Forms]![frmDocumentReviewReports]![cboMonth] Text ( 25 ), [Forms]![frmDocumentReviewReports]![cboYear] Text ( 25 );
SELECT qryDocument_Review_Stats_Crosstab.Proj_Acronym, qryDocument_Review_Stats_BusinessArea.Date_Received, qryDocument_Review_Stats_Crosstab.Document_Title, qryDocument_Review_Stats_Crosstab.Doc_Type, qryDocument_Review_Stats_BusinessArea.Description, qryDocument_Review_Stats_BusinessArea.[TSG/CDRL/DID], qryDocument_Review_Stats_Crosstab.[Total Of ErrorResult], qryDocument_Review_Stats_Crosstab.[0], qryDocument_Review_Stats_Crosstab.[1], qryDocument_Review_Stats_Crosstab.[2], qryDocument_Review_Stats_Crosstab.[3], qryDocument_Review_Stats_Crosstab.[4], qryDocument_Review_Stats_BusinessArea.Num_Pages, qryDocument_Review_Stats_BusinessArea.Review_Time
FROM qryDocument_Review_Stats_Crosstab INNER JOIN qryDocument_Review_Stats_BusinessArea ON (qryDocument_Review_Stats_Crosstab.Doc_Type = qryDocument_Review_Stats_BusinessArea.Doc_Type) AND (qryDocument_Review_Stats_Crosstab.Proj_Acronym = qryDocument_Review_Stats_BusinessArea.Proj_Acronym)
WHERE (((Format([Date_Received],"mmmm"))=[Forms]![frmDocumentReviewReports]![cboMonth]) AND ((Format([Date_Received],"yyyy"))=[Forms]![frmDocumentReviewReports]![cboYear]));

 

[traingamer]

Change your subquery to use count and Group by:

SELECT Description, Count(Description) AS CountOfDescription
FROM ...
GROUP BY Description;


traingamer

 

[Terpsfan]

Thanks for your response. Unfortunately that didn't work. What I ended up doin was creating a function in the report.