If/Then scenario? Case scenario? 1

[MADDPM]

I think I am simply overthinking this and have given myself a headache in the process, but i can't seem to find a solution to my query problem.

I need a report to pull all faculty eligible for leave in 2009-10 (in a field [Eligibility_Date]) PLUS their next leave eligiblity.

I have a current report that uses a query that works fine - there is a parameter [AY (XXXX-XX)] for academic year from the [Eligibility_Date] field - when the linked report opens, it asks the user for which academic year they need to see.

Data combination: "qryAppointmentDetails-Active_Yes" + table "LEAVE"

The LEAVE table has a foreign key of [Employee_Number]. It also contains two records for each eligible faculty member - their next eligibility and the next eligibility after that.

However, what I need now is to open a similar report with this concept:

If [Eligibility_Date]=2009-10, then also include the "next" eligiblity record for each [Employee_Number] who was eligible in 2009-10. (Because of adjustments to leave schedules, the next leave is not always the same for each faculty member taking a leave in 2009-10.) I tried adjusting the parameter filter to "2009-10 AND > 2009-10" (returns nothing) and also "2009-10 OR > 2009-10" - (returns everyone; e.g. those eligible in 2010-11 and beyond). I need the report to show only the employees who were eligible in 2009-10 and then their correlated next eligiblity.

So, I've tried everything I can think of - nesting If/Then statements, Case statements, variables - and obviously, I'm either overthinking this - or I'm not up to snuff with writing code for the complexity.

Any ideas?

The query stands now as:
PARAMETERS [AY (XXXX-XX)] Text ( 255 );
SELECT [qryAppointmentDetails-Active Yes].Last_Name, [qryAppointmentDetails-Active Yes].Incl_in_Report, [qryAppointmentDetails-Active Yes].Active_Calculated, LEAVE.Eligibility_Leave_Type, LEAVE.Eligibility_Date, LEAVE.Alternate_Eligibility_Date, LEAVE.Eligibility_Notes, [qryAppointmentDetails-Active Yes].First_Name, [qryAppointmentDetails-Active Yes].Middle, [qryAppointmentDetails-Active Yes].Preferred_Name, [qryAppointmentDetails-Active Yes].Department_Program, [qryAppointmentDetails-Active Yes].Title, [qryAppointmentDetails-Active Yes].RANK, [qryAppointmentDetails-Active Yes].Employee_Number, [qryAppointmentDetails-Active Yes].Tenure_Code, IIf([Tenure_Code]="D","AY ELIGIBILITY PLACE HOLDER for Admin. Appt. or Other Type Leave"," ") AS Tenure_Code_Detail
FROM [qryAppointmentDetails-Active Yes] INNER JOIN LEAVE ON [qryAppointmentDetails-Active Yes].Employee_Number = LEAVE.Employee_Number
WHERE ((([qryAppointmentDetails-Active Yes].Incl_in_Report)="Yes") AND ((LEAVE.Eligibility_Date)=[AY (XXXX-XX)]))
ORDER BY [qryAppointmentDetails-Active Yes].Last_Name;

Thanks in advance for any help or suggestions.
-Colleen

 

[PHV]

SELECT ...
, nextLEAVE.Eligibility AS NextEligibility
FROM ([qryAppointmentDetails-Active Yes]
INNER JOIN LEAVE ON [qryAppointmentDetails-Active Yes].Employee_Number = LEAVE.Employee_Number)
INNER JOIN LEAVE AS nextLEAVE ON LEAVE.Employee_Number = nextLEAVE.Employee_Number
WHERE [qryAppointmentDetails-Active Yes].Incl_in_Report="Yes" AND LEAVE.Eligibility_Date_Date=[AY (XXXX-XX] AND nextLEAVE.Eligibility_Date_Date>[AY (XXXX-XX]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MADDPM]

Thank you so, so much, PH.

Your solution was so, so close. I changed the "NextEligibility: Eligibility" (which was returning a "Yes" value) to "NextEligibility: Eligibility_Date" which now returns the academic year value.

All the best-
Colleen