variable start date for reports

[Scott]

I have a report that summarizes attendance occurances for my team of
50 employees. Right now the date values are entered by the user
before
report execution.

I want the start date to be flexible... I built a query which shows
all reps on an active attendance write up (within the last 180 days).
I want the attendance report to use the query's write up date as the
start date for each employee (if an active write up exists), and if
no
active write up exists, then use the current date minus 180 days. The
code would need to loop through each employee to determine the start
date of the report. The end date will be the current date for all
employees. The report is a summary of all reps so the variable start
time seemed tricky to me. Please let me know if there is any other
information needed.


Thanks-


Scott

 

[Jerry Whittle]

Show us the SQL of the query that you already built. Open the query in
design view. Next go to View, SQL View and copy and past it here.

 

[Scott]

Show us the SQL of the query that you already built. Open the query in
design view. Next go to View, SQL View and copy and past it here.

Here is the SQL for the attendance report:

SELECT tblEmployees.EmpFName, tblEmployees.EmpLName, -
Sum([tblAttendance].LS) AS [Late Starts], -Sum([tblAttendance].LB) AS
[Late Breaks], -Sum([tblAttendance].LL) AS [Late Lunches], -
Sum([tblAttendance].SwipeError) AS [Swipe Errors],
Sum(tblAttendance.UnscheduledTime) AS SumOfUnscheduledTime,
Sum(tblAttendance.UnpaidTime) AS [Unpaid Time]
FROM tblEmployees INNER JOIN tblAttendance ON tblEmployees.EmplID =
tblAttendance.EmplID
WHERE (((tblAttendance.AttendanceDate) Between [StartDate] And
[EndDate]))
GROUP BY tblEmployees.EmpFName, tblEmployees.EmpLName
ORDER BY tblEmployees.EmpLName;



Here is the SQL for the corrective action query that I want to pull
the start date from:
SELECT tblEmployees.EmpName, tblCorrective.CorrectiveDate,
tblCorrective.CorrectiveType, tblCorrective.CorrectiveLevel,
tblEmployees.ReviewerName
FROM tblEmployees INNER JOIN tblCorrective ON tblEmployees.EmplID =
tblCorrective.EmplID
WHERE (((tblCorrective.CorrectiveDate)>=DateAdd("d",-180,Date())) AND
((tblCorrective.CorrectiveType)="Attendance") AND
((tblEmployees.EmpTermDate) Is Null))
ORDER BY tblEmployees.EmpName;


Thanks-

Scott