Field-Dependent Query

[CaptainBob007]

Hi -

I'm not sure how exactly to classify this type of query or how to implement, but I'll try to explain what I need as best I can and hopefully somebody will understand.

I have a table of complaints. Each complaint (simplified) has the following fields:

CaseID
Problem
Complainant
Notes

CaseID is the primary key. Problem and Complainant are Numerical ID's relating to another table of properties.

I'm trying to make a query where when a given property is searched for, it will return (if any) the following 3 fields:

CaseID
Type
Notes

Where TYPE is equal to "Problem" or "Complainant" depending if the property was in the Problem or Complainant field of the original table.

Any ideas or tips on how I could do this would be greatly appreciated! Thanks a bunch!

~Bob

 

[PHV]

Take a look at UNION query:
SELECT CaseID, 'Problem' AS Type, Notes
FROM tblComplaints WHERE Problem = [Enter property]
UNION SELECT CaseID, 'Complainant', Notes
FROM tblComplaints WHERE Complainant = [Enter property]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244