interesting dynamic querying - advice needed

[PhilipEwen]

Hi,
I have a contact database which by selecting tick boxes additional forms appear with list boxes. there are currently 2 tickboxes/list boxes. 1 for industry sectors and 1 for client relations type.
Therefore if a user ticks the boxes, ( as an example ) they can search for all companies within "construction" AND "engineering" who are "existing client" OR "ex-client". All this data is read from a single company table.

The list boxes allow for the selection of multiple criteria - these are then parsed to the main search form. Once 'Search' is clicked, a query is built with these criteria. All fairly easy.

Added functionallity is now required to allow users to search under staff to company relations. Because more than one staff member can be related to a company, a separate table has been created which holds the staffid and company id.

Therefore what i want to do is search for all companies within "construction" AND "engineering" who are "existing client" OR "ex-client" AND in my "staff to company table" where companyid=staffid AND companyid=staffid ( for multiple staff )

I was wondering if a rather complex query can be built to do this, or if a better way would be to generate a dynamic table based on the first lot of results ( industry sectors and client relation ) and then re-query that table to work out the staff to company relation.

Any advice is welcome ( and code snippets too !! )

Many thanks

Phil.

 

[dgm]

Try this:
Create a new query in the query designer.
Add your two tables and link them. Add some dummy data into the criteria fields you wish to search against. Try it out to see if it returns the records it should. Then from the View menu, select SQL View. There is your query. All u have to do then is replace the dummy data with the data pulled from your form.

The query would look something like this:

SELECT DISTINCT aCompanies.companyID, aCompanies.companyName
FROM aCompanies LEFT JOIN aStaff ON aCompanies.companyID = aStaff.companyID
WHERE (((aCompanies.industry)="Engineering" Or (aCompanies.industry)="Construction") 
    AND ((aCompanies.clientType)="ex" Or (aCompanies.clientType)="existing") 
    AND ((aStaff.companyID) Is Not Null));

Hope that helps, although i'm not sure what u meant by
companyid=staffid. Aren't they two seperate things?

 

[PhilipEwen]

Hi,
Thanks for this. I will try this in the morning and let you know.

Thanks again,

Phil.