quick SELECT query question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have a quick question about SELECT queries....

say I have an Access database called tests containing the following fields:
userid, test_date, test_type.

Now, on a form, I have a drop-down list box for month (monthvar), another
for year (yearvar), and one for test types (testtypevar). I select the
criteria I want and hit a button to run a SELECT query against my database.

What I want to do is to have a SELECT query, if possible, to get the unique
# of userid's that have more than 1 test type that matches the month, year,
and test type that I selected on my form (ie. in May, 2005, there were 3
unique userid's that had 2 or more "Blue" tests).

Can this be done with a simple SELECT query? I can retrieve results for all
tests matching the selected criteria and put into a RecordSet fine, but it's
not unique by userid nor does it ignore the records that only had one test
for that month and year. I know about the SELECT DISTINCT command; it's the
counter of the 2 or more "Blue" tests that's getting me. Here's the query I
currently have:

"SELECT distinct(userid) FROM [tests] WHERE
ucase(trim(test_type))='"&testtypevar&"' and month(test_date)="&monthvar&"
and year(test_date)="&yearvar&" GROUP BY patient_id", objConn, ,
adLockReadOnly, adCmdText

TIA!

Tom


.

Quantcast