How do I return MaxDate as well as no date

[SteveC]

I'm trying to state that every employee less less than max date is incomplete or greater than is complete for some training. This works fine however, I need to list employees with no entry as incomplete as well.

Here are my two queries:

SELECT tblDepartments.Department, tblEmployees!LastName & ", " & tblEmployees!FirstName AS Name, Max(tblTrainingRecordGround.Date) AS MaxOfDate, tblEmployees.Department
FROM tblDepartments INNER JOIN (tblTrainingRecordGround INNER JOIN tblEmployees ON tblTrainingRecordGround.Employee = tblEmployees.ID) ON tblDepartments.ID = tblEmployees.Department
GROUP BY tblDepartments.Department, tblEmployees!LastName & ", " & tblEmployees!FirstName, tblEmployees.Department, tblEmployees.ID, tblTrainingRecordGround.TrainingType, tblEmployees.CurrentlyEmployed
HAVING (((tblEmployees.Department)=6) AND ((tblTrainingRecordGround.TrainingType)=3) AND ((tblEmployees.CurrentlyEmployed)=Yes));



SELECT QrySMS2.tblDepartments.Department, QrySMS2.Name, IIf([MaxOfDate]>#12/31/2008#,"Complete","Incomplete") AS Status
FROM QrySMS2;


Thanks in advance for any help

 

[HiTechCoach]

I'm trying to state that every employee less less than max date is incomplete or greater than is complete for some training. This works fine however, I need to list employees with no entry as incomplete as well.

Here are my two queries:

SELECT tblDepartments.Department, tblEmployees!LastName & ", " & tblEmployees!FirstName AS Name, Max(tblTrainingRecordGround.Date) AS MaxOfDate, tblEmployees.Department
FROM tblDepartments INNER JOIN (tblTrainingRecordGround INNER JOIN tblEmployees ON tblTrainingRecordGround.Employee = tblEmployees.ID) ON tblDepartments.ID = tblEmployees.Department
GROUP BY tblDepartments.Department, tblEmployees!LastName & ", " & tblEmployees!FirstName, tblEmployees.Department, tblEmployees.ID, tblTrainingRecordGround.TrainingType, tblEmployees.CurrentlyEmployed
HAVING (((tblEmployees.Department)=6) AND ((tblTrainingRecordGround.TrainingType)=3) AND ((tblEmployees.CurrentlyEmployed)=Yes));



SELECT QrySMS2.tblDepartments.Department, QrySMS2.Name, IIf([MaxOfDate]>#12/31/2008#,"Complete","Incomplete") AS Status
FROM QrySMS2;


Thanks in advance for any help

First, I would avoid using reserved words as User Defined Names.

... AS Name ...

would be better as something like:

... AS EmplNameLF ...

Date is also a reserved word.

If you are going to Keep using Name and Date as field names, I would urge you to always wrapped then with [ and ] to keep Access from confusing your field names with function names and properties names.


If the issues is with this: Max(tblTrainingRecordGround.[Date])

where the date field may be null, then try:

Max(Nz(tblTrainingRecordGround.[Date],#1/1/1900#))

 

[SteveC]

Thanks! I will work on those field names. I tried your code and it returns the same values. What I'm trying to return is an "Incomplete" If they have not completed the course this calendar year or ever. That part is working fine provided they have taken the course in the past what I cant get my head around is how to return it if they have never completed the course. i.e No entry at all.

 

[HiTechCoach]

Thanks! I will work on those field names. I tried your code and it returns the same values. What I'm trying to return is an "Incomplete" If they have not completed the course this calendar year or ever. That part is working fine provided they have taken the course in the past what I cant get my head around is how to return it if they have never completed the course. i.e No entry at all.

Have you tried an unmatched query?

 

[SteveC]

I tried that today and with no luck as the example I found had two related fields. In this Database there is the Employee table, Departments Table, Training type table and the table that stores the training records by employee and training type and date. I need to return training that an employee has no entry for by type as well as by expiry date. Does this makes sense?. I know I'm probably missing something simple

Steve

 

[SteveC]

I guess I didnt explain that very well! My unmatched query returns employees with no records at all in the training table. What i need is too get records of people with no entry of a certain "TrainingType"and add this to my previous query.

Thanks in advance for any help.

 

[Thinh]

from what you explain. it seems you need an left or right join depending on which table you want include. you want to include everything in table that has the non matching records and the everything in the lookup table.