How to Count Unique using WHERE clause MS Access query
Posted on 2009-04-06
I am having trouble figuring out how to get a query to return the count of unique account numbers in a particular table in my db.
The query first links two tables, 'time_code_ref_tbl' to 'SumEarnings_tbl' on field 'cycle_date'. The purpose of this is to use a user defined 'time_code' as specified in a textbox on the main form in the db for the query criteria.
For example when '0209' is entered in the form's text box, I would like the query to return the count of unique account numbers for the two cycledates that correspond to 0209 as according to the 'time_code_ref_tbl'. Here is some SQL I am statring with.
(It doesn't seem like putting in 'DISTINCT' does anything)
SELECT DISTINCT Count(SumEarnings2009.GroupAccount) AS CountOfGroupAccount
FROM SumEarnings2009 INNER JOIN TIME_CODE_REFERENCE_TABLE ON SumEarnings2009.CycleDate=TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE
WHERE (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=Forms![GEM to CMR ENTER DIFFERENCES FORM]!Time_Code_txt));