SELECT queries

  • Thread starter Thomas J. Brooks, Jr.
  • Start date
T

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
 
G

Guest

Thomas J. Brooks said:
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
Click to expand...

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
 
T

Thomas J. Brooks, Jr.

Steve,

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

Tom

SteveS said:
Thomas J. Brooks said:
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
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL SELECT statement 1
Subquery or not ? 3
Query with dates 2
Select Subquery: To only display one year at a time 11
Need Help on getting the query to come back with a blank value if thevalue is equal to the criteria 5
Help with more than on expiration Date 2
Help combining Queries 2
Subquery doesn't return GUIDs 1

Top