I have a table of employee dependents and want to be able to identify those
who are 21 or older and not in school as these dependents are no longer
eligible for coverage under our plan. Is there an efficient way of setting
up my table to pull this information half yearly or yearly? I have heard of
a status field but am unsure how to accurately set this up.
--

Your table needs two things: (1) a date of birth field and (2) a field that
shows if they are in school or not. If you have those two fields, it would be
simple.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Pat" wrote:

> I have a table of employee dependents and want to be able to identify those
> who are 21 or older and not in school as these dependents are no longer
> eligible for coverage under our plan. Is there an efficient way of setting
> up my table to pull this information half yearly or yearly? I have heard of
> a status field but am unsure how to accurately set this up.
> --
>

To do this, the following 2 fields must be in your table of dependents:
Date of birth
Some way of indicating whether this person is or is not in school. This
could be a yes/no field.
Assuming that these two fields are named BirthDate and IsInSchool (of type
Yes/No), the query to select ineligibles would be:
SELECT * FROM tblDependents WHERE DateDiff("yyyy, BirthDate", Date()) >= 21
AND IsInSchool=0
To select eligible dependents:
SELECT * FROM tblDependents WHERE DateDiff("yyyy, BirthDate", Date()) < 21
OR IsInSchool<>0
--
Ted