Report to display only records containing a value in one of X fiel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which will include four fields for four possible related
issues. The issues will be from a list we have of existing possible answers.
The user will only see a form. The form will have four empty boxes called
"Issue 1," "Issue 2," "Issue 3," and "Issue 4." All four of these boxes will
be a list box containing the same list of possible answers.

So, for example, say the user then wants to find all entries for which
"Employee was rude to customer" was an answer. The problem is, however, that
one person may have entered it in issue 1, one may have in issue 2, maybe
five did in issue 3, and seven did in issue 4. Is there a way to find them
all where this answer exists in at least one of the four related fields?

If it would be easier, I can just have it find every record where it exists
at all. That wouldn't be a problem, because it wouldn't exist anywhere other
than those four fields. Can anybody tell me how to accomplish this through
either of the ways I mentioned? Many thanks if you can.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
Consider normalizing your table structure so that each issue creates a
record in a related table. If that isn't possible, create a union query so
that you can search using standard SQL. Assuming this is a customer
complaint table with a primary key of ContactID.

SELECT ContactID, [Issue 1] as Issue
FROM tblComplaints
WHERE [Issue 1] is not null
UNION ALL
SELECT ContactID, [Issue 2]
FROM tblComplaints
WHERE [Issue 2] is not null
UNION ALL
SELECT ContactID, [Issue 3]
FROM tblComplaints
WHERE [Issue 3] is not null
UNION ALL
SELECT ContactID, [Issue 4]
FROM tblComplaints
WHERE [Issue 4] is not null;

You can now select * from your union query where Issue equals something.
 
Back
Top