Form filter by check box

[Guest]

I have a table and a corresponding form with personal data and several yes/no
checkboxes for years (checkbox for 2006, box for 2007, 2008) indicating the
the individual is participating in a program for that year. I would like the
form to display only individuals with the box checked for 2008, for example.
I would like the filter to occur automatically upon opening the form
everytime.

 

[Guest]

Sorry to be the bearer of bad news, but your design is flawed. You should
NOT have repeating columns (in this case a column for each year). You should
have another table which is a junction table which will allow you to add a
record which would have the person's ID and the year. Then, you would have
no problem limiting them.

The way you have it now, you will have to basically redesign the query each
year to get only those for a certain year. That is definitely not good
RELATIONAL Database design.

I think you may need to do some reading on Normalization:

http://support.microsoft.com/kb/283878

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[Guest]

hi
it seems you'll have to redesign your table every year (adding a new check
box for that year)
a better way to do it is seperate [personnel] table and [years] table like
that:
[personnel] will hold a pID (primary key) and other attributes of the person
like name etc.
another table, [Years] will have 2 columns: pID (foreign key from Personnel)
and year (the actual number "2006", "2007" etc.)
this way any 1 person from [personnel] table will have a varying number of
related records in [Years] table, each representing his paticipation in that
year.
then you will be able to ask the database for example, to find all those
that have a record in [Years] with the number "2008"... like you wanted

hope it all makes sense to you
Erez.

 

[Stacky]

I am not really convinced that what you are trying to do is impossible using
your current data.

From what I can understand you have one form which has a table that many
check boxes. I am guessing from your post that you only want to see records,
in a list, that have one of these check boxes ticked. If this is the case,
just create a query that selects all the personel info you want to display
and apply a filter to that query where the 2007 field = true. Then set the
rowsource of the list to equal the new filtered query and bingo!