user parameter - user to enter one or more values

[Mitchell_Collen]

Hi.
I want to create a query that will alow a user to search only one drug or
more drugs depending on user's needs. For instance, a user can see activity
for only asprin or he can see activity for asprin and zantac.

select drug, username, date
from drugactivity
where drug = @drug or drug = @drug or drug = @drug or drug = @drug;

Is there an easier way to do this?

Thanks in advance.
Misty

 

[Jeff Boyce]

It all starts with the data ...

Please provide more specific information about what the underlying data
looks like.

For example, are you trying to look in a single field? If so, what data
type is it? What are some examples of what data is contained in that field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

Add the values in a table, as primary key of that table, and use an inner
join:

SELECT...
FROM originalTable INNER JOIN tempTable
ON originalTable.drug = tempTable.drug


Hoping it may help,
Vanderghast, Access MVP

 

[Mitchell_Collen via AccessMonster.com]

I have 1800+ different drugs that can be ordered and referenced in the table
drugactivity.

user Mike
may want to find all records with asprin only.

user Mary
may want to final all records of drugs asprin, ibuprofen, zantac, and pepcid.

The data already exists in the drugactivity table. It's just I want the query
to dynamically allow the user to search on one value or choose to search on
more than one value. I was thinking the where clause can contain something
like where drug = @drug and drug = @drug maybe I can put a for count list. I
just don't know how to create it using the syntax available.

Thanks, Misty

 

[Michel Walsh]

User Mike fills his temp table (on his front end db) with one record, with
the value aspirin. Use Mary fills her temp table (on her front end db) with
four records, each with values aspirin, ibuprofen, zantac, and pepcid. Each
user uses the same query:

SELECT drugs.*
FROM drugs INNER JOIN temp ON drugs.drug=temp.drug


An inner join, in such case, IS like a WHERE clause based on an IN( list ),
except that the inner join allows what you want, while an IN( list ) does
not allow it as efficiently, or as easily, or as fully manageable.


When I say that the user fills the table, in fact, your application does it
for him/her. See http://www.mvps.org/access/reports/rpt0005.htm for an
example.



Hoping it may help,
Vanderghast, Access MVP

 

[Mitchell_Collen via AccessMonster.com]

Thanks for clarification. I will try this and see how it works.
-Misty

User Mike fills his temp table (on his front end db) with one record, with
the value aspirin. Use Mary fills her temp table (on her front end db) with
four records, each with values aspirin, ibuprofen, zantac, and pepcid. Each
user uses the same query:

SELECT drugs.*
FROM drugs INNER JOIN temp ON drugs.drug=temp.drug

An inner join, in such case, IS like a WHERE clause based on an IN( list ),
except that the inner join allows what you want, while an IN( list ) does
not allow it as efficiently, or as easily, or as fully manageable.

When I say that the user fills the table, in fact, your application does it
for him/her. See http://www.mvps.org/access/reports/rpt0005.htm for an
example.

Hoping it may help,
Vanderghast, Access MVP

I have 1800+ different drugs that can be ordered and referenced in the
table

[quoted text clipped - 32 lines]