Dear Gautam:
SELECT DISTINCT UserID
FROM tbTest T
WHERE (SELECT COUNT(*) FROM tbTest T1
WHERE T1.UserID = T.UserID
AND Testtype IN ("Test1", "Test2", "Test3")) = 3
This assumes no user will have taken any test more than once. That
is, UserID / Testtype is unique. If not, you could use a DISTINCT
selection in the subquery, which I can show you if you need it.
In a way, this is cheating. It says if the number of tests out of the
three selections is 3, then they have taken the 3 tests. Not only is
the above query susceptible to the problem of someone taking a test
twice, but it may not be flexible if changes are needed.
If you wanted to know who had taken at least 2 of the 3 tests, it wil
modify very easily. Just change the "= 3" to ">= 2"
But other logic changes may not be so simple. The literally correct
query would be:
SELECT DISTINCT UserID
FROM tbTest T
WHERE EXISTS (SELECT * FROM tbTest T1
WHERE T1.UserID = T.UserID
AND T1.Testtype = "Test1")
AND EXISTS (SELECT * FROM tbTest T1
WHERE T1.UserID = T.UserID
AND T1.Testtype = "Test2")
AND EXISTS (SELECT * FROM tbTest T1
WHERE T1.UserID = T.UserID
AND T1.Testtype = "Test3")
This is more logically exact and needs no protection against anyone
taking a test twice, but lacks flexibility in the case of the "2 out
of 3 tests" case suggested before. But, because it is logically
precise, it may be less confusing to some other programmer later on.
If this is not just a one-time scratch query need, a pretty good
solution would be to place the names of the tests desired in a
separate table, make a join to them, and build rules to test this as
needed. A lot of power and ease of use could be generated this way.
Hi,
I have a table: tbTest with the following fields:
userId, Testtype.
The data I have is:
UserID: (1,1,1,2,2,3)
Testtype: (Test1, Test2, Test3, Test1, Test2, Test1)
I want to write a select query to retrieve all
users(UserID) who have taken (Test1 AND Test2 AND Test3)
Is it possible to do it using just Select query (or is it
necessary to do some ASP coding)
Thanks
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
