Return records if null

[Sabosis]

I have a database to show employee attendance records. If an employee is on a corrective action, a query will show their corrective action date and any attendance occurences since that date. What it does not show is employees on a corrective action who have not had any occurrences since the corrective action took place.
A criteria in the query is set to show occurences greater than the corrective date, this is why employees without occurences since the corrective date are left off of the query. Can i modify the criteria to have them included? Do i create a second query to show these employees, then join the 2 queries in a 3rd new query? Please advise, any help is appreciated.

[ranman256]

Q1: qsCorrectives, shows all emps with a corrective date (tCorrectives table)...select empid, EventDate, Event (where [Event] ='corrective')

Q2: qsOccurAfterCorrDate, join tCorrectives table to qsCorrectives on EMPID and set criteria:
tCorrectives.Date > qsOccurAfterCorrDate.Date

[ssanfu]

Welcome to the forum.......


Realize that we know nothing about your dB. You ask "Can i modify the criteria to have them included?". It is possible. It depends on your dB design.
Would you post the SQL of the query?


How do you determine an employee has a "corrective action"? A Date field?
How do you determine "attendance occurrences" A Date field?

One option is to modify the existing query.
Another is to create a new query that finds employees that have a "corrective action" but no "attendance occurrences" from a specified date, then join the two queries in a union query.