0

I have a table, Contact Info

I have a form for user input, frmSearch

I have a query that is the subject of my question, frmSearchQuery

The table has data about residents which are First, last, phone, email, address, .... And about 25 mixed education and skills.

Use Case

The idea is that my client can check some boxes in a fool-proof form, and it'll spit out residents that mach the selected skills that they can then provide to potential employers (Section 8/Low income housing, my client is trying to help their residents find steady employment)

The example use case is me selecting GED and HVAC and having a list of all residents that have a GED and HVAC experience.

I have a SQL query that obviously doesn't work quite the way I want it, I'm trying to rack my brain thinking of how I can write this so that basically if skill is selected on form = true and resident skill = true then it'll match the query parameters.

How close am I to that goal with the below code?

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = True AND [Some School] = True)
  AND (Forms!frmSearch!chk_ged  = True AND [GED] = True)
  AND (Forms!frmSearch!chk_highschool  = True AND [High School Diploma] = True)
  AND (Forms!frmSearch!chk_somecollege  = True AND [Some College] = True)
  AND (Forms!frmSearch!chk_certificate  = True AND [Certificate] = True)
  AND (Forms!frmSearch!chk_associates  = True AND [Associate Degree] = True)
  AND (Forms!frmSearch!chk_bachelors  = True AND [Bachelor Degree] = True)
  AND (Forms!frmSearch!chk_masters  = True AND [Masters Degree] = True)
  AND (Forms!frmSearch!chk_accounting  = True AND [Accounting/Bookeeping] = True)
  AND (Forms!frmSearch!chk_admin  = True AND [Administrative/Clerical/Office Training] = True)
  AND (Forms!frmSearch!chk_computer  = True AND [Computer Skills] = True)
  AND (Forms!frmSearch!chk_plumbing  = True AND [Plumbing] = True)
  AND (Forms!frmSearch!chk_concrete  = True AND [Concrete Finishing] = True)
  AND (Forms!frmSearch!chk_groundskeeping  = True AND [Grounds Keeping] = True)
  AND (Forms!frmSearch!chk_HVAC  = True AND [HVAC] = True)
  AND (Forms!frmSearch!chk_casemanagement  = True AND [Case Management] = True)
  AND (Forms!frmSearch!chk_roofer  = True AND [Roofer] = True)
  AND (Forms!frmSearch!chk_carpentry  = True AND [Carpentry] = True)
  AND (Forms!frmSearch!chk_electrical  = True AND [Electrical] = True)
  AND (Forms!frmSearch!chk_bricklayer  = True AND [Brick Layer] = True)
  AND (Forms!frmSearch!chk_receptionist  = True AND [Receptionist] = True)
  AND (Forms!frmSearch!chk_heavyequipment  = True AND [Heavy Equipment Operation] = True)
  AND (Forms!frmSearch!chk_painting  = True AND [Painting] = True)
  AND (Forms!frmSearch!chk_drywall  = True AND [Dry Wall Installation] = True)
  AND (Forms!frmSearch!chk_sheetmetal  = True AND [Sheet Metal Work] = True);

EDIT

@Andre actually helped me with this earlier, with the below, but it only matches residents with EVERY skill/education checked. I tried messing around with the boolean values to see if I get different results and forgot to paste the original statement provided to me.

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = False OR [Some School] = True)
  AND (Forms!frmSearch!chk_ged  = False OR [GED] = True)
  AND (Forms!frmSearch!chk_highschool  = False OR [High School Diploma] = True)
  AND (Forms!frmSearch!chk_somecollege  = False OR [Some College] = True)
  AND (Forms!frmSearch!chk_certificate  = False OR [Certificate] = True)
  AND (Forms!frmSearch!chk_associates  = False OR [Associate Degree] = True)
  AND (Forms!frmSearch!chk_bachelors  = False OR [Bachelor Degree] = True)
  AND (Forms!frmSearch!chk_masters  = False OR [Masters Degree] = True)
  AND (Forms!frmSearch!chk_accounting  = False OR [Accounting/Bookeeping] = True)
  AND (Forms!frmSearch!chk_admin  = False OR [Administrative/Clerical/Office Training] = True)
  AND (Forms!frmSearch!chk_computer  = False OR [Computer Skills] = True)
  AND (Forms!frmSearch!chk_plumbing  = False OR [Plumbing] = True)
  AND (Forms!frmSearch!chk_concrete  = False OR [Concrete Finishing] = True)
  AND (Forms!frmSearch!chk_groundskeeping  = False OR [Grounds Keeping] = True)
  AND (Forms!frmSearch!chk_HVAC  = False OR [HVAC] = True)
  AND (Forms!frmSearch!chk_casemanagement  = False OR [Case Management] = True)
  AND (Forms!frmSearch!chk_roofer  = False OR [Roofer] = True)
  AND (Forms!frmSearch!chk_carpentry  = False OR [Carpentry] = True)
  AND (Forms!frmSearch!chk_electrical  = False OR [Electrical] = True)
  AND (Forms!frmSearch!chk_bricklayer  = False OR [Brick Layer] = True)
  AND (Forms!frmSearch!chk_receptionist  = False OR [Receptionist] = True)
  AND (Forms!frmSearch!chk_heavyequipment  = False OR [Heavy Equipment Operation] = True)
  AND (Forms!frmSearch!chk_painting  = False OR [Painting] = True)
  AND (Forms!frmSearch!chk_drywall  = False OR [Dry Wall Installation] = True)
  AND (Forms!frmSearch!chk_sheetmetal  = False OR [Sheet Metal Work] = True);

EDIT 2:

Here's the form. Default state false, triple state no. Everything unchecked. Doesn't matter what I have checked though in this form because the result is always the same enter image description here

When I run it, I only get one result, a dummy entry with everything set check (set to TRUE). enter image description here

Not sure why though! This is with the query under my first EDIT.

If I try to run my search with just "Associates Degree" checked, I should expect three results. Two actual entries that have an Associates, and my dummy entry, however, only the dummy entry shows up.

Improve this question
4
  • Also, the listed "skills" are indeed kinda all over the place, but this is what the client wants, and those are the skills listed on a paper checklist provided to all residents. Commented Dec 19, 2019 at 3:06
  • 1
    Using multiple yes/no fields that represent same type of data is not normalized structure and trying to apply filter criteria will always frustrate. But what does "doesn't work quite the way I want" mean? What's wrong with output? Commented Dec 19, 2019 at 4:10
  • @June7, just that my current SQL query doesn't match the results I'm after. I'm not familiar with SQL and Access (obviously haha!) and I don't understand what normalized structure means. My client has a bunch of residents, and either they do (TRUE) have certain skills or don't (FALSE). Why would a checkbox be inappropriate for a true/false value? Commented Dec 19, 2019 at 14:27
  • You should research 'normalization'. I didn't say anything wrong with checkbox for true/false, I said multiple yes/no fields for non-normalized data structure will be frustrating when trying to filter. Also, I don't use dynamic parameterized queries - I use VBA to build filter criteria and apply to form or report. Commented Dec 19, 2019 at 19:59

1 Answer 1

Reset to default
1

As I wrote in my original answer, the logic only works by having OR in the clause for each skill.

Also the check for the search boxes must be for FALSE, not TRUE.

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = False OR [Some School] = True)
  AND (Forms!frmSearch!chk_ged        = False OR [GED] = True)
  AND (...
                                          ^   ^
                                          |   |

Maybe it helps understanding the logic by reversing it:

Boolean logic says: (A or B) == not (not A and not B)

i.e. you DON'T want to list records, where you DO search for a skill, and the person DOESN'T have the skill

i.e.

  AND NOT (Forms!frmSearch!chk_ged = True AND [GED] = False)

Edit: it turned out that you have two checkbox named differently in the query vs. the form.

Form:  chk__concrete
Query: chk_concrete

Form:  chk_carpet
Query: chk_carpentry

It's kinda annoying that Access doesn't ask for these parameters when running the query. Instead (I guess) it silently takes it as NULL, and these clauses don't work.

Correct these checkbox names in form design, and the query works.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Right! I tried the one you suggested initially, but the only way it'll match something on the table is if I make a dummy resident with every skill/education checked. What the form has (true or false) doesn't matter, it'll always show that one dummy resident.
Make sure that all checkboxes have DefaultValue = False and TripleState = False. Then it must work. @Ghawblin
Curious. Everything seems correct. Can you make a copy of your database, remove all personal info (just leave 2-3 test records in it), compact and zip it, and upload to a file hoster like mediafire.com so I can have a look? @Ghawblin
Well. This method only works, if all control names in the query actually exist, see edit.
I have no words. I am the worlds biggest dingus. You have helped me through the majority of this, thank you so much. At this point I just need to built out the front end more so it looks nice. I'm embarrassed that I required this much hand-holding, but I'm smarter for it. I hope you have a fantastic holiday and thank you so so much. This project is going to help a lot of people in my community. It's for a local housing authority, people who need government assisted living. I bit off more than I can chew taking this, I work in Cybersecurity and not at all with SQL bits. Thank you
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.