SELECT queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thomas J. Brooks, Jr. - 19 May 2005 17:52 GMT

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

Reply to this Message

SteveS - 20 May 2005 08:07 GMT

> I have a quick question about SELECT queries....
>
[quoted text clipped - 26 lines]
>
> Tom

Try this:

'**** Begin Code ******
Dim strSQL As String

strSQL = "SELECT DISTINCT tests.userid, "
strSQL = strSQL & " FROM tests "
strSQL = strSQL & " GROUP BY tests.userid, tests.test_type, "
strSQL = strSQL & " Month([test_date]), Year([test_date]) "
strSQL = strSQL & " HAVING Count(tests.userid) > 1"
strSQL = strSQL & " AND tests.test_type = '" & [Forms]![form9].[testtypevar]
& "' "
strSQL = strSQL & " AND Month([test_date]) = " & [Forms]![form9].[monthvar]
strSQL = strSQL & " AND Year([test_date]) = " & [Forms]![form9].[yearvar] &
";"

'**** End Code ******

If testtypevar, monthvar and yearvar refer to controls on a form, replace
"Form9" with the name of your form that the controls are on. If they refer to
global/module variables, remove the "[Forms]![form9]." part.

If you want to experiment, create a new query, switch to SQL view and paste
in the following:

SELECT DISTINCT tests.userid, Count(tests.userid) AS CountOfuserid,
tests.test_type, Month([test_date]) AS TheMonth, Year([test_date]) AS TheYear
FROM tests
GROUP BY tests.userid, tests.test_type, Month([test_date]), Year([test_date])
HAVING (((Count(tests.userid))>1) AND
((tests.test_type)=[forms]![form9].[testtypevar]) AND
((Month([test_date]))=[forms]![form9].[monthvar]) AND
((Year([test_date]))=[forms]![form9].[yearvar]));

Again, change "Form9" to the name of your form that the controls are on.

HTH
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message

Thomas J. Brooks, Jr. - 20 May 2005 21:14 GMT

Steve,

Thanks! The HAVING COUNT part was what I needed. Worked like a champ!

Tom

>> I have a quick question about SELECT queries....
>>
[quoted text clipped - 85 lines]
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)

Reply to this Message

SteveS - 21 May 2005 03:47 GMT

You're welcome.

---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message