How to create a query/queries which will search based on checkbox selections.

[JKeeper]

Hi guys!

I recently just started with Access about a week ago and I'm trying to take a fairly small excel spreadsheet and convert it over to an access database. The information is for company information, certifications, services offered, and contact info. I've given each of the four categories their own table and I'm trying to build in a search function for the services offered. I've googled around quite a bit and no solution has worked quite right especially when dealing with single selections.



SO! For the services offered, I made the table and the different fields as Yes/No data types. What I want to do is have a form pop up that would allow you to check the boxes of each of the services required of the firm, and have a list of the firms which meet those requirements pop up. So I created a query using the design view which would check from the form if the checkbox was checked and if so, assign the criteria a value of -1 which from my understanding means true in Access 2010. However, when I run the query, it only shows the results which match exactly. This becomes a problem when I want to look for a single service offered from the database, but it will only spit out the result which only has that one service offered, even if other companies offer that service and more.

As I said, I started recently with Access, so I'm still trying to get my head around this. If there's an easier way of doing it or if you have any ideas on how to implement this feature that'd be fantastic.

Thank you guys!

[CJ_London]

sounds like you data is not normalised - often the case with data originating from excel. In which case that will be the first thing you will need to sort out.

Suggest provide some example data as you have it at the moment and then provide an example output from that data

[JKeeper]

Didn't import the data directly, I'm going to input the data or transfer it over by hand at a later point when I have most of the framework and forms of the database actually built. So right now I have four tables:

1. Company table with stuff like a name, company id, notes, website
2. Contact table with stuff like company id, first name, last name, position, phone, email
3. Certifications, with Yes/No based on whether a checkbox is checked or not
4. Services offered, with a Yes/No based on whether a checkbox is checked or not.

Tables 2-4 are linked in a one to many relationship with the Company table through the company ID. So for the test data I put in, there are three companies. A, B, C, each of these have survey, and geotech skills.

Company A has geotech checked, company B has survey checked, and company C has both checked.

The query which I'm running is this:

SELECT companyDatabase.[Company Name], servicesOffered.[Survey/Right-of-Way], servicesOffered.[Geotech Engineering]
FROM companyDatabase INNER JOIN servicesOffered ON companyDatabase.[Company ID] = servicesOffered.[Company ID]
WHERE (((servicesOffered.[Survey/Right-of-Way])=IIf([Forms]![test]![checkSurvey]=-1,-1,0))) OR (((servicesOffered.[Geotech Engineering])=IIf([Forms]![test]![checkGeotech]=-1,-1,0)));

This is what SQL view gives me.

Now my issue becomes is if I put the AND operator instead of OR, whenever I click a single checkbox, it'll only return a company which has exactly only one service offered even if other companies have that service. If I put the OR operator, if I look for a company with multiple services, it returns everything because only one of the conditions has to be true.

For example, if I check the survey checkbox, and not the geotech one, it'll return fine with OR, but not with AND
if I check the geotech but not the survey, it'll return fine with OR, but not with AND
If I check both boxes, it'll return all companies rather than company C which has both checked.

I understand why it's failing the way it is, but based on my limited knowledge of Access, it's what I have to work with. So, my question is if there's any way to create the search the way I need it to be.

[CJ_London]

ANDs and ORs are like multiplication and division. you need to use brackets to clarify the order of processing.

All criteria returns either true or false, there ar no mights or maybes. either something = something else or it doesn't.

First your criteria can be simplified - true/false =yes/no=-1/0. Booleans are stored as numbers (and can actually be interpreted as 0=false, anything else is true). In Access there is also a 3rd 'tri-state' alternative value of null (which means unknown) but should not be used.

servicesOffered.[Survey/Right-of-Way]=[Forms]![test]![checkSurvey]

servicesOffered.[Geotech Engineering]=[Forms]![test]![checkGeotech]

First you need to consider your data - what options are there - can both survey and geotech both be true or false, or can only one of them be true. or geotch can only be true if survey is true etc

You need to decide what you are looking for but in principle this will find one or the other providing one is ticked and the other is not

([Forms]![test]![checkSurvey]=servicesOffered.[Survey/Right-of-Way] AND NOT [Forms]![test]![checkGeotech]) OR ([Forms]![test]![checkSurvey]=servicesOffered.[Survey/Right-of-Way] AND NOT [Forms]![test]![checkSurvey])

if both are ticked you would add another OR to the criteria

....OR ([Forms]![test]![checkSurvey]=true AND [Forms]![test]![checkGeotech]=true)

[JKeeper]

Got it! It makes a lot more sense when you start adding more OR or AND operators to the query. Thanks for the help, one last question if you're able. I will eventually be doing this for 16 different services offered. Is there a more optimal way of designing this type of search? Regardless thanks for your help, I really appreciate it!

[CJ_London]

Is there a more optimal way of designing this type of search?

yes - it is a common question and there are plenty of solutions. All basically either building the query or using form filtering. See at the bottom of this thread for links to similar questions on this forum or google something like 'access search forms'.

So rather than taking into account all the possible permutations, only take into account those that are relevant. It requires the use of code to build the criteria, but in principle, using the above example you would have something behind a 'search now' button along the lines of

dim sqlstr as string

if checkSurvey then sqlstr=sqlstr & "[Survey/Right-of-Way]=true OR "
if checkGeotech then sqlstr=sqlstr  & "[Geotech Engineering]=true OR "
if len(sqlstr)<>0 then 'something has been selected for criteria so remove final ' OR '
    sqlstr=left(sqlstr,len(sqlstr)-4)
    me.filter=sqlstr
    me.filteron=true
end if

obviously this gets more complicated when you want a more complex criteria like "survey=true and surveydate=yesterday and person attending=fred or survey=false and surveydate=tomorrow and person attending has not been specified"

but sit down with pen and paper, remember the only options are true and false and build you logic.

A couple of tips. Avoid using spaces and non alphanumeric characters in tables and field names - at best you have to use square brackets, at worst, access gets confused. It's a common newbie error which just creates work. Use the field caption property for 'complex' names, that is what it is for.

I would also suggest that if you have multiple checkboxes is a single record, your data is not normalised (thinking of your tables 3 and 4 above)- it may be valid but under normal circumstances I would expect one record per checkbox. Data that is not normalised makes for extremely complex and slow queries - perhaps so complex they wont even run.

so unnormalised is

companyFK..Cert1....Cert2....Cert3
1...................T.........F..........T

normalised your would have

CompanyFK...Cert
1..................Cert1
1..................Cert3

note the normalised table does not have a record for Cert2 because it is false - the fact a record exists indicates it is true

And with the normalised form you can just add another record if a new certification comes along, no other change required. With the unnormalised form you will need to modify the table and change queries forms and reports

[JKeeper]

Awesome, thank you so much. I'll rethink how I want to do the schema of the overall database and get to work.

Once again thank you for all your help!

[orange]

Further to ajax's great comments, here is a thread with several links re database design and planning.

Good luck with your project and welcome to Access and the forum.