I'm trying to limit the records being selected for a report and running into a complication.
I have 3 Tables being accessed in my report.
e_emp: names, employee ids
e_emp_dtl: status (active, inactive, temp)
e_evnt_smry_hdr: events that occurred, timestamps, transaction numbers, clock in time, clock out time
The goal of the report is to display events that occurred outside of the times an employee was clocked in. It works fine for all employees except those who are inactive status. When an employee is listed as inactive then no time records are written into the db, so all events an inactive employee performs wind up on the report.
Statuses can be added for future dates for an employee. For example, I may hire an employee on January 1 as a temp, and then later add a new status that shows he has been hired as an active employee on February 3rd, add a status of inactive beginning March 8th when he hurts his back, and add a new status of active beginning August 6th as his medical release indicates he can start working normally again on that date. Managers add the status changes as soon as they know that they are needed and usually are doing so 1-2 weeks prior to the date that the status will become effective.
In my e_emp_dtl table the fields that get new entries as statuses are added are eff_date_time and emp_stat_id. A stat_id of 2 = inactive, so using the above dates as an example I would want to exclude events that occurred between March 8th and August 6th for that employee.
How can I do this in Crystal? My current attempts to check status only seem to pull the first record in the e_emp_dtl table for the employee and an employee can have several records here.
I have 3 Tables being accessed in my report.
e_emp: names, employee ids
e_emp_dtl: status (active, inactive, temp)
e_evnt_smry_hdr: events that occurred, timestamps, transaction numbers, clock in time, clock out time
The goal of the report is to display events that occurred outside of the times an employee was clocked in. It works fine for all employees except those who are inactive status. When an employee is listed as inactive then no time records are written into the db, so all events an inactive employee performs wind up on the report.
Statuses can be added for future dates for an employee. For example, I may hire an employee on January 1 as a temp, and then later add a new status that shows he has been hired as an active employee on February 3rd, add a status of inactive beginning March 8th when he hurts his back, and add a new status of active beginning August 6th as his medical release indicates he can start working normally again on that date. Managers add the status changes as soon as they know that they are needed and usually are doing so 1-2 weeks prior to the date that the status will become effective.
In my e_emp_dtl table the fields that get new entries as statuses are added are eff_date_time and emp_stat_id. A stat_id of 2 = inactive, so using the above dates as an example I would want to exclude events that occurred between March 8th and August 6th for that employee.
How can I do this in Crystal? My current attempts to check status only seem to pull the first record in the e_emp_dtl table for the employee and an employee can have several records here.