SQL SELECT statement

[Thomas J. Brooks, Jr.]

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

say I have a simple 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

 

[Sylvain Lafontaine]

This newsgroup is about Access Active Data Project accessing a SQL-Server as
the backend and is not about Access databases (MDB files) or Access'
queries. The syntax between Access' SQL and T-SQL for SQL-Server is
different, so you won't get here proper information about your problem.

You should ask your question to microsoft.public.access.queries, for
exemple.

However, in your case, you should probably use a group by on userid and add
a HAVING clause on Count (*); something like:

Select UserId, PatientId .... From ....
Group By UserId, PatientId
Having Count(*) >= 2

From there, you can then transform your query into a subquery to retrieve
only the distinct values of UserId:

Select distinct SQ.UserId from (Select UserId, PatientId, ........) as SQ

where SQ is the name (alias) of your subquery. You can also try to use the
Distinct clause directly on the query with the Group By and the Having
clauses but I'm not sure of the syntaxe here.