And/Or Search

[office_guru]

I have a table listing companies and their capabilities identified via some 20 check boxes (yes/no fields). I'd like to use a form for users to use to identify companies that match the capabilities checked off.

I attempted to do this by creating a parameter query (=[forms]![frmname]![fieldname]) that feeds off of the inputs of the form. It partially works, the problem is it's only returning everything that's exactly the same (ie if you select 10 capabilities and there's a company with those 10 capablilities plus more capablities that company will not be returned in the result.) I'm guessing I need to do some sort of and/or query however can not get my arms around it so that it would show companies with all the capablities showing plus any additional capablitities.

I looked into the cascading combo box samples in the forum however there is no descending criteria I can apply and don't think that would be applicable in my situation.

Any insight, input would be greatly appreciated.

 

[Mile-O]

I have a table listing companies and their capabilities identified via some 20 check boxes (yes/no fields). I'd like to use a form for users to use to identify companies that match the capabilities checked off.

Your problem is that uou have designed your table incorrectly by putting a repeating group into your table - this means that your table is not meeting the first normal form (1NF) of database design (normalisation). Ideally you want to get your table to third normal form (3NF).

You need a many-to-many relationship to simulate the fact that one company can have many capabilities and one capability can apply to many companies. So, you need two more tables.

The ideal structure would be:

tblCompanies
CompanyID
CompanyName
etc.

tblCapability
CapabilityID
Capability

tblCompaniesToCapabilities
CompanyID
CapabilityID

Then, with this you can use a simple subform to allow the selection as normal for your companies and, when it comes to searching, you can make use of a listbox that allows multiple selections.

 

[Mile-O]

An example of the structure in action.

 

[office_guru]

Makes Sense

SJ McAbney,

Thanks for the input and the sample db, it makes sense. I have forms that users use to input the capabilities so I think I'm going to try and create queries to run the tables you recommend and hopefully that should work.

 

[Mile-O]

There are also examples around about how to make a query from a multiple query listbox. Here is one of mine.