How to select most current date by month, day and year

[Design by Sue]

My datebase is tracking employee training, Each employee is required to complete the training every 12 months (for example) The database tracks all of the info about the employee and I have a table that keeps the records of all training completed dates for all employees for all SOP numbers (the training courses). I have created a report to show the employees that have expired training. I currently have ExpiredDate: DateAdd("m",[Expiration],[DateCompleted]) as my code but that I believe is only searching for the most current month but does not take into consideration the rest of the date. I need to select only the most recent of the dates based on the entire expiration date. Can someone please correct my code to show the results based on the entire date?

Thanks

 

[Gasman]

but that I believe is only searching for the most current month but does not take into consideration the rest of the date.

No, it just gives you a date, that the course needs to be renewed.

 

[Design by Sue]

Sorry - I was wrong in that! I have a report that shows the current date and I need the report to show only the SOPs that have expired as of the date on the report.

 

[Gasman]

I think you would need MAX() on the expiration date which is less than Date.
You could run a report each week that shows whose courses are going to expire in the following week/month?, being procactive, rather than waiting for the courses to expire.

 

[DHookom]

Consider creating a totals query that groups by employee and course with the max of the training date. Use this max date to determine if it is more than one year ago. A more complete answer would require the structure of your significant tables and fields.

 

[The_Doc_Man]

What you need is TWO fields in your individual training records. (At least two.) One of them is a flag that says "superseded" or something else that means that this old record has become irrelevant. If Joe Schmidlap takes "Operator Level 1" training and then a year later takes it again, you want ALL PREVIOUS RECORDS for "Joe Schmidlap" taking "Operator Level 1" to become superseded so that they don't figure into the "overdue" computation. The second field you need (and probably have) is the date on which the training occurred.

In a normalized setup, you would also have a table of available training and an expiration amount, which could be in any units you want. You would want a number and a unit. The unit would match the units used by DateAdd, which would include years, months, weeks, days, ... whatever. The amount would be the number of those units for expiration computation.

So then when doing the computation, you can do this either of two ways. EITHER

A. Compute the date of interest by using DateAdd( [units], -[expiration-amount], Date() ) to get the critical date
B. Compute the expiration date by using DateAdd( [units], [expiration-amount], [TestTakenDate] ) to get the critical date. Further, for (B) you must choose the only "test taken" record for that person and test combination for which the "superseded" flag is NOT set.

For (A), if the computed date is GREATER than the (only) non-superseded date that the test was taken, it is overdue.
For (B), if the expiration date is LESS than Date() (= today), it is overdue.

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Just watch out for "y" vs "yyyy" and "w" vs "ww" as the units in question. There is a reflexive tendency to use Now() as a date source, but Now() also includes time-of-day elements and thus could give you a confusing answer on the exact due date. That's why I specified Date() and not Now().

 

[Design by Sue]

I think you would need MAX() on the expiration date which is less than Date.
You could run a report each week that shows whose courses are going to expire in the following week/month?, being procactive, rather than waiting for the courses to expire.

Yes I have that report also an will be fixing that next. I will look up MAX and see if I can figure it out. Thanks

 

[Pat Hartman]

I think you want a query with this as the Where Clause

Where Max (DateAdd("m",[Expiration],[DateCompleted])) < Date()

If you want something other than today's date, use a different field in place of the Date()

 

[Design by Sue]

I think you want a query with this as the Where Clause

Where Max (DateAdd("m",[Expiration],[DateCompleted])) < Date()

If you want something other than today's date, use a different field in place of the Date()

I tried this but I am getting "you tried to execute a query that does not include the specified expression "SOP Number" as part of an aggregate function". Any thoughts?

 

[Gasman]

Show your whole query.
We are not mind readers.

 

[Design by Sue]

Show your whole query.
We are not mind readers.

Yeah I get that - I will look at this again on Monday and start with a fresh query - the one I am using must be buggered. Will post back.

Pam - thanks for the MAX code

 

[Pat Hartman]

It's Pat but you're welcome

 

[Design by Sue]

I figured out my problem. This query results in all training completed - the entire database. If I add <Date() as the criteria for the ExpDate I get all of the records for the training all employees that are before the current date BUT I need to filter this somehow so that the results are ONLY the most currently completed training for each employee, not every date in the history. So hopefully this will make it understandable:

Employee Number 1 has completed class ABC 4 times in the past years such as is shown below
Employee Number SOP Number Date Expires
1 ABC 9/20/25
1 ABC 8/24/24
1 ABC 6/24/23
1 ABC 9/20/22
In the case of employee 1, there should be no record in the report because he has completed the training and is not due to retake the course until 9/20/25

Employee Number SOP Number Date Expires
2 ABC 8/24/24
2 ABC 6/24/23
2 ABC 9/20/22
In the case of employee 2 the report only needs to display the training that expired on 8/24/24 because it is past due for renewal.

Employee Number SOP Number Date Expires
3 ABC 9/20/25
Again in the case of employee 3 there should be no record in the report because again the training is not due until next year.

I am sure I understand my problem now but can't see the logic or possible programming to select only the most recent of the trainings before the current date and report on them only.

SELECT [Employee TBL].[Last Name], [Employee TBL].[First Name], [Main TBL].[Employee Number], [Main TBL].[SOP Number], Format([Main TBL]![SOPVersion],"""-v""00") AS SOPVersion, [Main TBL].DateCompleted, [SOP TBL].Description, [SOP TBL].Type, [SOP TBL].Expiration, [Employee TBL].Inactive, SOPVersionTBL.Obsolete, [Last Name] & ", " & [First Name] AS FullName, [Employee Job Description TBL].[Job Description], [Employee Job Description TBL].Current, Max((DateAdd("m",[Expiration],[DateCompleted]))) AS ExpDate
FROM (([SOP TBL] INNER JOIN ([Employee TBL] INNER JOIN [Main TBL] ON [Employee TBL].[Employee Number] = [Main TBL].[Employee Number]) ON [SOP TBL].[SOP Number] = [Main TBL].[SOP Number]) INNER JOIN SOPVersionTBL ON (SOPVersionTBL.SOPVersion = [Main TBL].SOPVersion) AND (SOPVersionTBL.[SOP Number] = [Main TBL].[SOP Number]) AND ([SOP TBL].[SOP Number] = SOPVersionTBL.[SOP Number])) INNER JOIN [Employee Job Description TBL] ON ([Employee TBL].[Employee Number] = [Employee Job Description TBL].[Employee Number]) AND ([Employee TBL].[Employee Number] = [Employee Job Description TBL].[Employee Number])
GROUP BY [Employee TBL].[Last Name], [Employee TBL].[First Name], [Main TBL].[Employee Number], [Main TBL].[SOP Number], Format([Main TBL]![SOPVersion],"""-v""00"), [Main TBL].DateCompleted, [SOP TBL].Description, [SOP TBL].Type, [SOP TBL].Expiration, [Employee TBL].Inactive, SOPVersionTBL.Obsolete, [Last Name] & ", " & [First Name], [Employee Job Description TBL].[Job Description], [Employee Job Description TBL].Current
HAVING ((([Employee TBL].Inactive)=False) AND ((SOPVersionTBL.Obsolete)=False) AND (([Employee Job Description TBL].Current)=True) AND ((Max((DateAdd("m",[Expiration],[DateCompleted]))))>Date()))
ORDER BY [Main TBL].[Employee Number], [Main TBL].[SOP Number];

 

[Gasman]

Always start off with as little as you need to test the logic. Not that huge pile of text, that is not even formatted to be easily read. Google online sql formatter.
You likely need max on the date that is < date(), possibly grouped by employeeid.
That could then be linked to the rest of the data you need.
I am not that great at getting everything all in one query, and am more than happy to break it down into two.
That first is possibly the criteria for the second query.

However you do it, understanding the requirement is a key factor to start with.

 

[DHookom]

I'm not sure you read or understood my earlier reply. Create this totals/group by query and use it to find the most recent date a SOP was completed.

SELECT [Main TBL].[Employee Number], [Main TBL].[SOP Number],
  Format([Main TBL]![SOPVersion],"""-v""00") AS SOPVers,
  Max([Main TBL].DateCompleted) As MostRecentCompletion
FROM [Main TBL]
GROUP BY [Main TBL].[Employee Number], [Main TBL].[SOP Number],
  Format([Main TBL]![SOPVersion],"""-v""00")

 

[Design by Sue]

I'm not sure you read or understood my earlier reply. Create this totals/group by query and use it to find the most recent date a SOP was completed.

SELECT [Main TBL].[Employee Number], [Main TBL].[SOP Number],
  Format([Main TBL]![SOPVersion],"""-v""00") AS SOPVers,
  Max([Main TBL].DateCompleted) As MostRecentCompletion
FROM [Main TBL]
GROUP BY [Main TBL].[Employee Number], [Main TBL].[SOP Number],
  Format([Main TBL]![SOPVersion],"""-v""00")

Thank you - sorry I must have missed your post. I needed to use the expiration date not the completed date but I added the SOP table to the query and it looks like this might be working! So if i have it correct now I create a second query to use this information and select only those that expire before the current date. I so appreciate your help. Too often my "Access Brain" can't see the clear path and i keep learning with all of the help on this board.

SELECT [Main TBL].[Employee Number], [Main TBL].[SOP Number], Format([Main TBL]![SOPVersion],"""-v""00") AS SOPVers, Max((DateAdd("m",[Expiration],[DateCompleted]))) AS Expr1
FROM [Main TBL] INNER JOIN [SOP TBL] ON [Main TBL].[SOP Number] = [SOP TBL].[SOP Number]
GROUP BY [Main TBL].[Employee Number], [Main TBL].[SOP Number], Format([Main TBL]![SOPVersion],"""-v""00")
HAVING ((([Main TBL].[Employee Number])="3588") AND (([Main TBL].[SOP Number])="GMP-001"));