Creating a user friendly Query/Form on multiple fields

[onamission5]

Hello again!

Im looking to create a user friendly query which will need to search 3 fields (Sector1, Sector2 and Sector3) and pull out resquested data.
The 3 sector fields belong to a "Company" table.
Each company can be asssigned up to 3 sectors e.g. Sector1 - IT, Sector2 - Marketing, Sector3 - Finance. The query would need to search the 3 Sectors and display the relvant data.

I can see that having the 3 sector fields makes it more complex as some records will for example contain "IT" in Sector1 whereas another record may contain "IT" in Sector2 and therfore the query will have to search all 3 Sector fields for matching data.

If I setup an SQL based query, how can I then create a form where the user can then define the search criteria which the Query will then act upon?

Thanks

 

[dickohead]

I'd create a query with CompanyName, Sector1, Sector2 and Sector3, then in the criteria field for CompanyName I'd put:
[Sector1]=IT
[Sector2]=IT
[Sector3]=IT

To return all companies where any of the sectors are IT, correct?

Then to make that more user friendly, I'd change the "IT" bit to ["Which sector were you looking for? (IT, Marketing or Finance")]

Hope it helps...

 

[John Big Booty]

This cool search tool might be what you are looking for.

 

[onamission5]

Thanks for the responses.

I have created a Union query which searches the 3 sectors for a value. Here is the coding below.

SELECT CompanyReference, Company, Town, Postcode, CompanySector1, CompanySector2, CompanySector3
FROM Company
WHERE CompanySector1= "IT"
UNION
SELECT CompanyReference, Company, Town, Postcode, CompanySector1, CompanySector2, CompanySector3
FROM Company
WHERE CompanySector2= "IT"
UNION SELECT CompanyReference, Company, Town, Postcode, CompanySector1, CompanySector2, CompanySector3
FROM Company
WHERE CompanySector3= "IT"
ORDER BY Company;

I would now like to be able to create a form where the user can select which Sector they would like to seach (via a drop down combo box) and then code the "Where" statements to pick up the criteria from that form. Does anyone know what the coding is for this?

Thanks