Query w/ 1 parameter - 16 different fields, same possible answer

[Tboartz]

I have a query based on a single table. I have within the query 16 fields
(Abrasion, Laceration, Puncture, etc...) that vary in name but have the same
response "Yes", "No", or is left blank. I am trying to figure out within this
one query how to set up a parameter that will search for "Yes" in any of the
16 fields, without having 16 different parameters pop up for the user. There
is also an additional related field (Other) that needs to be queried by
preferably the same parameter, if not another, but it can have any text
response written in or remain blank.

I was reading other questions and responses within this forum, but i do not
think that i have come across something applicable. Sorry if i am duplicating
any other question.

Any help would be much appreciated.

Terri

 

[Amy Blankenship]

I have a query based on a single table. I have within the query 16 fields
(Abrasion, Laceration, Puncture, etc...) that vary in name but have the
same
response "Yes", "No", or is left blank. I am trying to figure out within
this
one query how to set up a parameter that will search for "Yes" in any of
the
16 fields, without having 16 different parameters pop up for the user.
There
is also an additional related field (Other) that needs to be queried by
preferably the same parameter, if not another, but it can have any text
response written in or remain blank.

How bout something like:

SELECT * FROM YourTable WHERE
(Abrasion = [Enter Yes or No] OR
Laceration = [Enter Yes or No] OR
Puncture = [Enter Yes or No] OR...)
AND
(Other = [Enter what you want to search against in the Other field])

HTH;

Amy

 

[Tboartz]

I may have left some information out. This query contains more than the 16
fields. It is basically a query of the entire table. and what i am looking at
is pulling up records of each field. So if i want to see how many Injuries
have been "Laceration", I need to be able to run the query and have it apply
to only "Laceration". I then want to view the records associated with that
injury. The only other thing i have come up with is individual queries but i
really don't want to do that, I want to simplify this for myself and the
user.

Terri

I have a query based on a single table. I have within the query 16 fields
(Abrasion, Laceration, Puncture, etc...) that vary in name but have the
same
response "Yes", "No", or is left blank. I am trying to figure out within
this
one query how to set up a parameter that will search for "Yes" in any of
the
16 fields, without having 16 different parameters pop up for the user.
There
is also an additional related field (Other) that needs to be queried by
preferably the same parameter, if not another, but it can have any text
response written in or remain blank.

How bout something like:

SELECT * FROM YourTable WHERE
(Abrasion = [Enter Yes or No] OR
Laceration = [Enter Yes or No] OR
Puncture = [Enter Yes or No] OR...)
AND
(Other = [Enter what you want to search against in the Other field])

HTH;

Amy

 

[Pete D.]

I don't understand 16 fields query, are you saying that any one of the 16
could have a laceration. If so it sounds like a spreadsheet and not a
database. To find a key word put this in the criteria cell of the field.
Like "*" & [Type Injury] & "*" This will prompt you with a box asking for
Type Injury when you run the query. By Chance one of your field names is
Type Injury use a different prompt in the [] above. If all 16 fields could
have the injury you have another problem so post back.
Pete D.

I may have left some information out. This query contains more than the 16
fields. It is basically a query of the entire table. and what i am looking
at
is pulling up records of each field. So if i want to see how many Injuries
have been "Laceration", I need to be able to run the query and have it
apply
to only "Laceration". I then want to view the records associated with that
injury. The only other thing i have come up with is individual queries but
i
really don't want to do that, I want to simplify this for myself and the
user.

Terri

I have a query based on a single table. I have within the query 16
fields
(Abrasion, Laceration, Puncture, etc...) that vary in name but have the
same
response "Yes", "No", or is left blank. I am trying to figure out
within
this
one query how to set up a parameter that will search for "Yes" in any
of
the
16 fields, without having 16 different parameters pop up for the user.
There
is also an additional related field (Other) that needs to be queried by
preferably the same parameter, if not another, but it can have any text
response written in or remain blank.

How bout something like:

SELECT * FROM YourTable WHERE
(Abrasion = [Enter Yes or No] OR
Laceration = [Enter Yes or No] OR
Puncture = [Enter Yes or No] OR...)
AND
(Other = [Enter what you want to search against in the Other field])

HTH;

Amy

 

[Pete D.]

Opps, I missed the yes no fields. Previous post was right answer.

I don't understand 16 fields query, are you saying that any one of the 16
could have a laceration. If so it sounds like a spreadsheet and not a
database. To find a key word put this in the criteria cell of the field.
Like "*" & [Type Injury] & "*" This will prompt you with a box asking for
Type Injury when you run the query. By Chance one of your field names is
Type Injury use a different prompt in the [] above. If all 16 fields could
have the injury you have another problem so post back.
Pete D.

I may have left some information out. This query contains more than the 16
fields. It is basically a query of the entire table. and what i am
looking at
is pulling up records of each field. So if i want to see how many
Injuries
have been "Laceration", I need to be able to run the query and have it
apply
to only "Laceration". I then want to view the records associated with
that
injury. The only other thing i have come up with is individual queries
but i
really don't want to do that, I want to simplify this for myself and the
user.

Terri

I have a query based on a single table. I have within the query 16
fields
(Abrasion, Laceration, Puncture, etc...) that vary in name but have
the
same
response "Yes", "No", or is left blank. I am trying to figure out
within
this
one query how to set up a parameter that will search for "Yes" in any
of
the
16 fields, without having 16 different parameters pop up for the user.
There
is also an additional related field (Other) that needs to be queried
by
preferably the same parameter, if not another, but it can have any
text
response written in or remain blank.

How bout something like:

SELECT * FROM YourTable WHERE
(Abrasion = [Enter Yes or No] OR
Laceration = [Enter Yes or No] OR
Puncture = [Enter Yes or No] OR...)
AND
(Other = [Enter what you want to search against in the Other field])

HTH;

Amy

 

[John Spencer]

Sounds to me as if your structure is wrong.

I think you really need a table InjuriesReceived.

This table would have at least two fields.
Injury : What was the injury - Laceration, abrasion, etc
IDField: the value of the primary key in the current table.
DateField: optional field to record the date of the injury
Severity: optional field to record how bad the injury was.
OtherDesc: If the Injury type is other, record what that Other means in
this text field.

Then your query would simply be to return records from this table where
Injury = "name of injury"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================