Multiple results in a querie

  • Thread starter Thread starter Paula
  • Start date Start date
P

Paula

I have an employee data base that has separate fields for
different eligibility dates for our various benefits. I
want to set up a querie/report that will give me a list
of who is eligible for what on a given date. So if I put
in parameters to give me who is eligible between 7/1/04
and 7/31/04 in all the different benefits. Is this
possible and how?
 
Paula,

It would be easier it your elegibility date data was in a separate table
with a structure something like:

EmpID: LongInt
BenefitType: String (I'd actually make this a LongInt and add another table
that relates BenefitID to BenefitType)
ElegDate: Date/Time

However, since you have dates in multiple columns, your query will have to
look something like:

SELECT EmpID, EmpLastName, EmpFirstName, Benefit1Date, Benefit2Date,
Benefit3Date
FROM tbl_Employees
WHERE Benefit1Date BETWEEN [Start Date Range] AND [End Date Range]
OR Benefit2Date BETWEEN [Start Date Range] AND [End Date Range]
OR Benefit3Date BETWEEN [Start Date Range] AND [End Date Range]

Another way to do this would be to use a UNION query, something like:

SELECT EmpID, EmpLastName, EmpFirstName, "Benefit1" as BenefitType,
Benefit1Date as EligDate
FROM tbl_Employees
WHERE Benefit1Date BETWEEN [Start Date Range] AND [End Date Range]
UNION ALL
SELECT EmpID, EmpLastName, EmpFirstName, "Benefit2" as BenefitType,
Benefit2Date as EligDate
FROM tbl_Employees
WHERE Benefit2Date BETWEEN [Start Date Range] AND [End Date Range]
UNION ALL
SELECT EmpID, EmpLastName, EmpFirstName, "Benefit3" as BenefitType,
Benefit3Date as EligDate
FROM tbl_Employees
WHERE Benefit3Date BETWEEN [Start Date Range] AND [End Date Range]

HTH
Dale
 
Back
Top