How to Count Unique using WHERE clause MS Access query

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.

Any suggestions?

(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));
pgermanAsked:
Who is Participating?
 
gnovakhs2nConnect With a Mentor Commented:
Oh, i see

a bracket problem ... try the snippet
select count(x.GroupAccount) as CountOfGroupAccount from 
(
SELECT DISTINCT SumEarnings2009.GroupAccount AS GroupAccount
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))
)  x 

Open in new window

0
 
gnovakhs2nCommented:
try like this:

select count(x.GroupAccount) as CountOfGroupAccount from (
SELECT DISTINCT SumEarnings2009.GroupAccount AS GroupAccount
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)) as x
0
 
pgermanAuthor Commented:
I still can't get that to work, but I think I am just going to simplify things and break it up into two queries.  One that will return a unique list, the other to count from that query.

The end result will be the same to the user.

Thank you anyway for the quick response.

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
gnovakhs2nCommented:
thats exactly what this query does, probably in MS-Access Syntax you have to remove the "as" from "as x" at the end
0
 
pgermanAuthor Commented:
Its still giving me a 'Syntax error in the from clause'  
0
 
8080_DiverCommented:
I know this sounds too easy but, have you tried working in the non-SQL Design View of the Query Wizard?  
Create the query in the Query Wizard without trying to get the DISTINCT working (in other words, let it return all of the multpile instances).  Then right click to viw the properties of the query (or switch to the SQL view) and add the DISTINCT clause.
I understand that "real developers work with SQL and not GUI" but that's right up there with "real men don't cry" and "real men never, ever wear pink shirts". ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.