Date Formula - Most recent within certain time frame

[Jeff G]

I hit a little road block with creating a query that retrieves certain records with dates. I'll try to explain as simply as I can. I'm using this database to track a single event. The separate queries i need to retrieve are:

1. Events from current date to 89 days in the past (I"ll refer to this as "current") Formula used: Between Date() And Date()-89
2. Events from 90 days to 179 days in the past ("90+ days") Formula used: Between Date() And Date()-89
3. Events from 180+ days in the past. ("180+ days") Formula used: Between Date()-180 And Date()-1000



Each query will have the Name, date and other info I'll add to the query. The block I'm running into is I only want to retrieve the last (most recent) date entered. When I originally ran it with just the date criteria it retrieved all records in that date range and I only need the last date for the completed event.

Also, I don't know if I will keep having "false positives" by having the personnel showing on each of the queries. To further explain, If i have employee A complete the event once each month he'd be considered "Current" on completing the event. Here's what I'm trying to track: I need employees to complete a certain task once within a 90 day period and they're considered "Current." After 90+days they need to get evaluated while performing the event. After 180+ days they need to get retrained.

The problem I might run into is if I run a query for those date ranges I'll have a "Current" employee also show up on the "90+" and the "180+" queries as well, which would tell me that this person last completed the event 180 days in the past.

Any help is greatly appreciated. Let me know if any further clarification is needed.

V/R,

Jeff

[isladogs]

Item 2 should of course be Between Date()-90 And Date()-179

1. Run Query 1 & get the list of current employees

2. Run Query 2 on all employees NOT in Query 1.
To do so, create an unmatched query for the employee list & Query 1 ...then filter that for your date range

3. Run Query 3 on all employees NOT in Query 1 or 2 - similar approach to above

[Jeff G]

Excellent, thanks for the info. I'll give it a go. That's a typo on my part for the item 2, I have the correct one in the data sheet.

[isladogs]

You're welcome.
In case you aren't aware there is a wizard you can use for unmatched queries

[Jeff G]

Do i need to have the totals tab selected under Show/Hide and select either min/max? Here's my design, view, and my SQL.

[isladogs]

If you want the most recent date for each person then group by person and set date to Max

[Jeff G]

I have everything falling into place except for the "Flights - Current" Query. When I go to set the parameters for Total - Group By I'm having issues. Under "Date" I have it set to "Max". It only retrieves one record for that date and leaves out other names that were on that same date. Which I understand that. When I also have the Name column set to max the query is blank when I click Run. If I only select Max on the Name column only and leave the Date column Group By blank it only retrieves 5/7 personnel that have entries. Again I'm just trying to pull the most recent date a person completed the event on the Table I'm querying. Sorry for the repeated questions, Access is fairly new to me, but it's going to make things easy when all I have to do is maintain this database.

[isladogs]

Group by Name and set Date field to Max. Add Date again but this time set to WHERE and add your filter.
Note that the where filter field will be automatically unticked so it isn't displayed in the result

[ArviLaanemets]

For tasks like this I have written an UDF

---
Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
Dim dbs As Database
Dim rs As Recordset
Dim varQstr As String
' The function returns the value of RetField from table parTable
' where IdField equals IdCond and DateField is nearest past or equal to parDate.
' The parameter IdIsString must be True, when IdField has Text format

On Error GoTo Err_ValidValue
Set dbs = CurrentDb
varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
" WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
" And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
" ORDER BY [" & DateField & "] DESC"
Set rs = dbs.OpenRecordset(varQstr)
rs.AbsolutePosition = 0
ValidValue = rs.Fields(0).Value

Err_ValidValue:
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing
End Function

---

Current version works with non-date part of filter condition applied to single field (IdField). When you want to use more complex filter condition, then probably you have to use full WHERE clause string as parameter instead. The change to code will be minor though.

[Jeff G]

Isladogs, that did the trick. I very much appreciate your help. Thanks

[isladogs]

You're welcome