If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple results in a querie
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? |
#2
|
|||
|
|||
Multiple results in a querie
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 "Paula" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
return results based on multiple conditions | Drabbacs | Worksheet Functions | 3 | May 28th, 2004 11:34 PM |
multiple results search | ruchir | Worksheet Functions | 3 | March 4th, 2004 02:18 PM |