Three reports based on query with dates in 4 fields

[Mr C]

Datasheet View:
Master table has fields- EmpID, Dept, Name, and DateHired.
Has relationship (EmpID) to another table with 4 fields – all dates.
Employees need to have 3 shots as follows
A – within 30 days of DateHired
B – 30 days after first shot A
C – 180 days after second shot B
However, if the fourth field “is not null†don’t show in any of the reports,
shots A, B, and C are not needed.

So on the first of the month we need to run a report to show all employees
who are required to have the shots A
Then a report which shows all employees who need to have shot B
Then a report which shows all employees who need to have shot three C
No report is needed if the fourth field is not null, it is for employees who
do not need additional shots as they had a special one.
Once the all three shots are given, they are not needed again.

 

[Allen Browne]

In query design, in the Criteria row under your 4th field, enter:
Is Null

Save the query, and build your reports on that.

Instead of multiple date fields, it might be better to have multiple records
in a related table. The fields would be like this:
EmpID relates to the employee
ShotTypeID contains "A", "B", or ...
ShotDate when the employee had that type of shot

This won't simplify your existing issue, but it would help with others.

 

[John Spencer MVP]

Have not had shot 1
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date1 is Null AND
Shots.Date4 is Null

Have had shot 1 but not shot 2
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date1 is Not Null
AND Shots.Date2 is Null
AND Shots.Date4 is Null

Have had shot 2 but not shot 3
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date2 is Not Null
AND Shots.Date3 is Null
AND Shots.Date4 is Null

If you are trying to find those that are OVERDUE then you will need to modify
the queries to account for that.

Overdue for the first shot
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date1 is Null AND
Shots.Date4 is Null
And DateHired < DateAdd("d",-30,Date())

Overdue for the second shot
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date1 is Not Null
AND Shots.Date2 is Null
AND Shots.Date4 is Null
AND DateDiff("d",Date1,Date())>=30

Overdue for third shot
SELECT Employees.*
FROM Employees LEFT JOIN Shots
ON Employees.[Emp id] = Shots.[Emp Id]
WHERE Shots.Date2 is Not Null
AND Shots.Date3 is Null
AND Shots.Date4 is Null
AND DateDiff("d",Date2,Date())>=180



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County