query help
Hello,
I am in need of help with a query that I am using for a search results page for a company project database. On the search page, I have 9 search options (several of these with a drop down menu). Just to name a few, users can search by Owner, by State, by Market, and by Consultant. Each project will only have one "result" for any of these search options. The problem I am having is when I add the search option for Subcontractors (which has a drop down menu)--each project can have more than one Subcontractor. For example, project number 3024 has 3 subconstractors, and the query now returns 3 projects for the State of NY (because project 3024 is located in NY) when there is really only one project (3024) that is located in NY.
So how can I alter my query to allow the Subcontractor seach option to return results for all 3 subcontractors for 3024 (by searching 3 times by different subcontractors) but have the State search option return only one result for the state of NY instead of returning the same project 3 times.
Heres my query:
SELECT DISTINCT Project.PrjJobNumber, Project.PrjName, Project.PrjMarket, Project.PrjState, Owner.OwnOrg, ConsltComp.ConsltCompOrg, SubContrComp.SubContrCompOrg
FROM Project, Owner, crConslt, ConsltComp, crSubContr, SubContrComp
WHERE Project.PrjOwnerID=Owner.OwnID AND Owner.OwnOrg LIKE '%varOwn%' AND Project.PrjState LIKE '%varState%' AND Project.PrjMarket LIKE '%varMarket%' AND Project.PrjJobNumber=crConslt.crConsltJobNum AND crConslt.crConsltCompID=ConsltComp.ConsltCompID AND ConsltComp.ConsltCompOrg LIKE '%varConslt%' AND Project.PrjJobNumber=crSubContr.crSubContrJobNum AND crSubContr.crSubContrCompID=SubContrComp.SubContrCompOrg AND SubContrComp.SubContrCompOrg LIKE '%varSubcontr%'
Thanks in advance,
Lori