yes/no query

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I am building a contacts DB and each contact falls into
one or more categories, which I have set to yes/no (this
displays the little tick box in the table).
How do I create a query that lists all the members of one
of the categories. I have tried LIKE "yes" but this
doesn't appear to work.
Also the individuals and organisations fall into two
different tables which are related. Is the best way to
display the organisation info next to an individuals on a
form to use a subform?
Cheers,
Mark (access newbie)
(I'm using access 2002)
 
I am building a contacts DB and each contact falls into
one or more categories, which I have set to yes/no (this
displays the little tick box in the table).
How do I create a query that lists all the members of one
of the categories. I have tried LIKE "yes" but this
doesn't appear to work.
Also the individuals and organisations fall into two
different tables which are related. Is the best way to
display the organisation info next to an individuals on a
form to use a subform?
Cheers,
Mark (access newbie)
(I'm using access 2002)
Click to expand...

Your "Yes" is a text expression which is different from Yes (as a
Number value).

Check Box fields have a value of either -1 (Yes) or 0 (No).
Set the criteria for a Yes/No check box field to
-1
For Yes values.
 
you're not clear on your individuals/organizations tables' structures or
relationship, so it's hard to answer your second question. but i wonder
about your table design.
it sounds like you have multiple category fields in a table - the
individuals' table? that is not normalized. if one person can be in many
categories, and each category can apply to many persons, that is a
many-to-many relationship. it should be modeled as follows:

tblIndividuals
IndividualID (primary key)
FirstName
LastName
other fields that describe the individual

tblCategories
CategoryID (primary key)
CategoryName

tblIndividualCategories
IndividualId (foreign key from tblIndividuals)
CategoryID (foreign key from tblCategories)
you can use the two fields to make a combination primary key. there is a
one-to-many relationship between the primary keys of the two "parent" tables
and the foreign keys in this table.
you can enter as many categories as you wish for each individual. you can
model the relationship for data entry, in a mainform
(tblIndividuals)/subform (tblIndividualCategories) setup.
suggest you Google for table relationships or table normalization. building
the rest of your db will be a lot easier if you get the tables set up
correctly.

hth
 
Back
Top