Yes/No Fields and Criteria in a Query

[omicronkappa278]

OK, here is what I got.

I have a List of customers in which I want to place them into Categories.

I have a 1 Table with the the Following Fields: Customer Name, Customer Email, Customer Phone, Category 1, Category 2, Category 3, Category 4



Each Category Field is a Yes/No Field.

I have a form to add new Customers and check each Category box in which they belong in. (They Can belong to more than one Category)... There is also a Form with each Category on it for the following query

So, I created a Query with the following Fields:

Customer Name, Customer Email, Customer Phone, and for Each Category, I pointed to the Form where I wanted to filter for this query: [Forms]![Report Filter Form].[Category 1], [Forms]![Report Filter Form].[Category 2], [Forms]![Report Filter Form].[Category 3], [Forms]![Report Filter Form].[Category 4]

Now when I run the Filter Form and then run the query is works OK... But it works in an "AND" relationship where If I check Category 1 & 2, then I get each customer which falls in Category 1 & 2, but not the ones which only fall in Category 1 and the ones which only fall in category 2.

Now I figured out that if I move each Formula on a separate line I get an "OR" situation... But it produces a situation where no matter what I check on my list of check boxes I get all records... I am guessing that there is nothing to specify in the query whether the box is check yes or no.

Any suggestions?

[CraigDolphin]

Classic case as to why you should normalize your table design.

You have an one to many relationship between cusotmers and categories. So you should have a Customers table, and a CustomerCategories table, along with a table of Categories.

So
Customers
CustomerID (auto, pk)
CustomerFName
CustomerLName
etc

Categories
CategoryID (auto, pk)
CategoryDescription

CustomerCategories
CustomerCategoryID (auto, pk)
CustomerID (fk)
CategoryID (fk)

Then you would simply add a row to the CustomerCategory table for each cateogry that applies to that customer.

Then you would query the CustomerCategories table limiting your results to those customers where the CategoryID = whatever. Or, if you want the flexibility to find a specific combination of categories only, you could use the IN(X,Y,Z) operator instead. The lists of values in the IN() operator's list can be created dynamically using code based on those values in the four checkboxes on your form, or by looping through a multi-select listbox which lists all the category options.

That way if the number of categories changes it's a trivial matter to update the database to reflect the new reality. Using multiple fields means that you'd need to redo your form, queries, and potentially any code that depended on the 'multiple fields' approach you have started with.