Query with true/false fields

[mike7]

I've created a table with Name, Address, Country and so forth along with 15 true/false type of fields. By using the query by form technique, I would like my users to check as many boxes as they wish and beeing able to extract Name, Address and Country satisfying these conditions.

I don't have a clue how to build that query. Is it possible to do it in design view or will I need some coding ?

Thanks for your help.

MIKE

 

[Mile-O]

I've created a table with Name, Address, Country and so forth along with 15 true/false type of fields.

By having 15 True/False fields I would immediately guess that you have a design flaw. This design flaw would violate the First Normal Form (1NF) of database design which is used to eliminate repeating groups and ensure atomic data. I wrote an FAQ on 1nf here.

Ensuring 1NF (and also 2NF and 3NF) will ensure that all problems are removed from the database and reduce the amount of reworking requred when, for example, you need to add a 16th True/False field.

So, I'd say, learn about Normalization. Although I don't know what the repeating group you have is, I'm guessing you have a many-to-many relationship which should be modelled into your database. This will actually require three tables.

 

[mike7]

more explanations are needed

You may be right about my design flaw but wrong about ensuring atomic data. I thought of splitting these 15 true/false (these fields are like ''your fields of interest'' where you can choose medecine, arts, sports, etc...So, if you like sports, you can check the checkbox) fields in two tables (that's probably what I'm going to do) but at the end I thought I'd be stuck again trying to extract my records based on any combination of these 15 variables containing the value ''true''.

Anymore thoughts ?

Mike

 

[Mile-O]

I don't see what I was wrong with regarding atomic data - I only made a statement as to its involvement in 1NF. Your data is just a repeating group which needs to be quashed.

So, the tables we'd need are:

tblPeople
PeopleID (Autonumber)
Forename
Surname
Address1
Address2
Address3
CountryID

tblCountries
CountryID (Autonumber)
Country

tblInterests
InterestID (Autonumber)
Interest

tblPeopleToInterests
PeopleID (Number)
InterestID (Number)


Each field ending in ID is linked in the relationships window with Referential Integrity enforced and Cascade Delete selected.

Once in this structure you can easily query a whole table (and only one column) for ALL options selected - a Multi-Select listbox is better than multiple checkboxes here.

 

[DALeffler]

Not to hijack this thread, but if you had:

tblPeopleToInterests
PeopleID (Number)
InterestID (Number)

and the table contents were something like:

[U]PeopleID[/U]     [U]InterestID[/U]
   1             1
   1             2
   1             3
   1             [I]n[/I]
   2             1
   2             2
   2             3
   2             [I]n[/I]
   [I]n[/I]             [I]n[/I]

How would you design a query that could show across the form and not down the form for each PeopleID? Like this:

Name     Interest1     Interest2     Interest3     Interest[I]n[/I]
  1          Y             Y             Y             Y
  2          Y             Y             Y             Y
  [I]n[/I]          Y             Y             Y             Y

 

[WayneRyan]

Doug,

http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

Wayne

 

[Jon K]

I've created a table with Name, Address, Country and so forth along with 15 true/false type of fields. By using the query by form technique, I would like my users to check as many boxes as they wish and beeing able to extract Name, Address and Country satisfying these conditions.

I don't have a clue how to build that query.

A horizontally structured table with multiple True/False fields is good for searching and showing multiple attributes, but bad for data manipulation and statistical analysis.

Putting table structure issues aside, you can easily build a search form based on a horizontal table. On the form you can set the Triple State property of the check boxes to Yes, so that each check box can have 3 values: Null, True and False.

When a check box on the form is Null, you let the query returns every record. When a check box is True, you let it return the True records. Otherwise, return the False ones.

I have attached a database as an illustration. In the query, I put the criteria in the Field cells and put <>False in the Criteria cells, so that Access will not re-arrange the columns and rows after the query is saved.

 

[mike7]

incredible

Jon K,

You've understood perfectly what I wanted to do. It still would need a little change. In your form, if I select Country=Canada and I check [field1], it returns me a record where [field2] is also checked. And I needed to find only the records where [field1] and only [field1] was checked. How would you modify your query ?

I'd also like to thank the rest of you for your ideas and suggestions.

Mike

 

[Jon K]

In the table, Field2 is either checked (meaning True) or unchecked (meaning False).

If what you want is Country=Canada, Field1 is True, and Field2 is False,
you can select Canada on the form, check Field1 and uncheck Field2.

Of course the table must have such a record, otherwise the query will return nothing.

 

[mike7]

Just great !

Jon K,

Your help and support were just great. For a newbie like me, there's nothing better than an example.

A nice thanks from Canada,

Mike