Working with 'yes/no' data types

[philhood2]

I have a 'record of service' table that has the following
fields:

EmployeeID
2000
2001
2002
2003
2004
200n

The employeeID field is numeric.
The other fields are 'Yes/No' data types (i.e. was the
person employed in this year, yes or no?)

I am trying to create a combo box list that includes only
those employees that worked in a particular year. The form
that contains the combo box also has a text control
containing a date that I want to use as the criteria.

In other words, if the form is dated 13/08/2004, I want to
include in the combo box list only those employees that
worked in 2004.

What's the best way to do this?

Any help greatly appreciated.

Thanks

Phil.

 

[Duane Hookom]

The best way is to normalize your table structure so you have two fields:
EmployeeID
ServiceYear
An employee with 3 years of service would have 3 records in the table. This
would allow you to easily filter the combo box based on the ServiceYear
field.