Queries with multiple Yes/No fields (1 Viewer)

cecypdel · Sep 9, 2019

Hello everyone!!!

I am trying to create a form to query entries that match certain parameters.

Let's say that I want a client name and ID list and that the yes/no values are 1) likes sports, 2) likes dogs, and 3) likes cats

I have unbound checkboxes on my form and I've been trying two different kinds of code on my query:

[Forms]![FormName]![Checkbox] - I've been leaving it as is since Access is supposed to automatically know that it's a yes/no field
Iif([[Forms]![FormName]![Checkbox]=True,True,False)

The problem I'm encountering is that if, for example, the "likes sports" value is not checked, the "likes dogs" value is checked, and the "likes cats" value is unchecked, instead of giving me just a list of clients who like dogs, it generates a list of clients who don't like sports, like dogs, and don't like cats.

I know there's probably an easy way to write the query criteria but I cannot for the life of me figure our how :banghead::banghead::banghead::banghead::banghead:

HELP PLEASE!!!!!

theDBguy · Sep 9, 2019

Hi. Welcome to AWF! Ignoring the query for now, what does your table structure look like? Do you really have a bunch of checkboxes (Yes/No field) in your table?

Mark_ · Sep 9, 2019

If you have multiple yes/no questions, I would personally ONLY save a child record for each that is true. This means you can add new parent "Do you like" questions without needing to add fields to your table.

This also means you can create a query that says "Show me all people records that have a child answer for the question "Do you like dogs?" by setting up the query on your answer table, linked to your people by your people tables ID, and limited to your question ID.

cecypdel · Sep 9, 2019

theDBguy said:
Hi. Welcome to AWF! Ignoring the query for now, what does your table structure look like? Do you really have a bunch of checkboxes (Yes/No field) in your table?

Yeah
Pretty much all of my fields are yes/no

It's for a clinical report
I want to find patients who have had x and/or y and/or z studies done

Mark_ · Sep 9, 2019

Is your list of studies fixed? i.e. for this application they will NEVER change?
If they are fixed, do you have it in writing and a way to go back and charge when they DO decide to change the list?

cecypdel · Sep 9, 2019

Mark_ said:
Is your list of studies fixed? i.e. for this application they will NEVER change?
If they are fixed, do you have it in writing and a way to go back and charge when they DO decide to change the list?

Yes and yes

Mark_ · Sep 9, 2019

Then for your query, you'll need to identify for each field if it need be true, need be false, or need be ignored. If you use a check box for each you'll be looking for EITHER true or false, but it sounds like you looking for something more complex that that.

How many total fields are involved?

cecypdel · Sep 9, 2019

Mark_ said:
Then for your query, you'll need to identify for each field if it need be true, need be false, or need be ignored. If you use a check box for each you'll be looking for EITHER true or false, but it sounds like you looking for something more complex that that.

How many total fields are involved?

Well, I am doing a test run with 12 fields
But the final query will have to involve 154

Mark_ · Sep 9, 2019

OK, I'd change your layout then.
I'd have a Parent file for studies, a Parent file for Patients, and a linking file that attaches studies to patients. This will make your job a LOT easier.

Then you can query the linking file for matches rather than trying to wade through dozens of fields.

This sample may do what you are looking for..
https://www.access-programmers.co.uk/forums/showthread.php?t=305454

cecypdel · Sep 9, 2019

Mark_ said:
OK, I'd change your layout then.
I'd have a Parent file for studies, a Parent file for Patients, and a linking file that attaches studies to patients. This will make your job a LOT easier.

Then you can query the linking file for matches rather than trying to wade through dozens of fields.
[/url]

Checking it out right now

Thank you!

Pat Hartman · Sep 10, 2019

Jumping on the normalization band wagon. You would use an unbound multi-select listbox. The base query would select all clients with one of the selections. The totals query counts the selections for each person and using a Having clause selects only people with a count = the number of selections made in the multi-select listbox. So if you select 5 items, the first query selects any client with any of the 5. The second query counts the selections and returns ONLY the people with a count of 5. Even though the queries are nested (you could use subqueries if you prefer but i don't because separate queries are much easier to test). Doing it your way will require a fair amount of coding because you will need to build the WHERE clause with VBA because having a WHERE clause with 100 optional criteria will be too difficult to manage.

Doing it the right way also protects you from future changes. Your code will not in any way be affected by the addition or deletion of one of the options. All you need (actually the user, not you) to do is to add an item to the Y/N options list. NO CODE CHANGES WILL EVER BE REQUIRED. Think about that while you think about what would happen if you stuck with your current design and the users needed to add/delete some option.

cecypdel · Sep 10, 2019

Pat Hartman said:
Jumping on the normalization band wagon. You would use an unbound multi-select listbox. The base query would select all clients with one of the selections. The totals query counts the selections for each person and using a Having clause selects only people with a count = the number of selections made in the multi-select listbox. So if you select 5 items, the first query selects any client with any of the 5. The second query counts the selections and returns ONLY the people with a count of 5. Even though the queries are nested (you could use subqueries if you prefer but i don't because separate queries are much easier to test). Doing it your way will require a fair amount of coding because you will need to build the WHERE clause with VBA because having a WHERE clause with 100 optional criteria will be too difficult to manage.

Doing it the right way also protects you from future changes. Your code will not in any way be affected by the addition or deletion of one of the options. All you need (actually the user, not you) to do is to add an item to the Y/N options list. NO CODE CHANGES WILL EVER BE REQUIRED. Think about that while you think about what would happen if you stuck with your current design and the users needed to add/delete some option.

Thank you!!!
I am super new to this
I haven't worked with complex databases since 2001 and I was using VB
So Access is a completely different approach.

Thank you for the listbox idea!

Pat Hartman · Sep 10, 2019

Access(Jet or ACE to be precise) actually isn't any different as a database engine from SQL Server, Oracle, DB2, etc. Coding is different but Schema design has not varied much since the 70's when SQL was first developed. The normalization rules have held fast. The SQL DML (data manipulation language) has gotten more sophisticated making certain types of queries much easier to create than they used to be but the actual Schema design has changed very little.

You will find that Access VBA is different from VB so watch yourself. They are similar but not the same.

Queries with multiple Yes/No fields (1 Viewer)

cecypdel

New member

theDBguy

I’m here to help

Mark_

Longboard on the internet

cecypdel

New member

Mark_

Longboard on the internet

cecypdel

New member

Mark_

Longboard on the internet

cecypdel

New member

Mark_

Longboard on the internet

cecypdel

New member

Pat Hartman

Super Moderator

cecypdel

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread