Query Criteria picking newest date.

[BatmanMR287]

I have a many table that has multiple entries for each policy number. I need to write a query that selects the most recent date for each policy and checks to see if it is longer than five years. The problem is that some of the older updates for each policy have dates that are more than five years old and I need to ignore those in the query.

[JoeM]

Can you post a small sample of your data, specifically that shows all the fields you want to return in this query?

[BatmanMR287]

The first table is the one, the second is the many. I need to return the policy number, name, and date. As you can see there are many revisions for each policy. I need to check the most recent date and return the policy if it is more than five years since the last update.

[BatmanMR287]

Also need to not show if it is deleted.

[JoeM]

If you just had a table named "PolicyTable" with two fields named "PolicyName" and "PolicyDate", you could use an Aggregate Query to get what you want.
The SQL code of that query would look something like this:

SELECT PolicyTable.PolicyName, Max(PolicyTable.PolicyDate) AS MaxOfPolicyDate
FROM PolicyTable
GROUP BY PolicyTable.PolicyName
HAVING Max(PolicyTable.PolicyDate)>DateAdd("yyyy",-5,Date());

[JoeM]

Just saw your other posts now.
For other fields that you wanted to show in the query, you would just add them both to the SELECT and GROUP BY clauses.

To exclude Deleted records, just add a WHERE clause after the FROM clause with that Criteria.

[BatmanMR287]

I have tried a query with just the Max portion in it and it has worked correctly pulling only the most recent dates. I will attempt to incorporate the rest into the query or make three to connect together. I know three is inefficient but this is a smaller database with a low number of users.

[JoeM]

I will attempt to incorporate the rest into the query or make three to connect together. I know three is inefficient but this is a smaller database with a low number of users.

I am a little confused...
Three what?
Are you talking about tables?
If so, first create a query that joins the tables and returns all the information you want.
Then apply the Aggregate Query and Criteria on those query results.

[BatmanMR287]

Sorry, there are three search criteria.
1. Most recent date.
2. Can't have deleted checkbox selected.
3. If most recent date is 5 years or more in the past.
This information is on two tables.

[JoeM]

Can you create a simple select query that joins these two tables and returns all the fields that you want? Be sure to include your Deleted Checkbox field.
Don't worry that it returns a lot more records than you want right now, we are just trying to get to a good starting point.

[JoeM]

Once you get the Select Query working correctly, here is all I think you will need to do after that.

1. In Query Design View, click on the Totals button (looks like a Sigma). This will add a Totals row under each field with the words "Group By" under each field.
2. Under the "Deleted" Check Box field, change the Totals row value from "Group By" to "Where", and type the word FALSE in the Criteria row of this field.
3. Under the Policy Date field, change the Totals row value from "Group By" to "Max", and enter the following in the Criteria row of this

> DateAdd("yyyy",-5,Date())

View the results. Does this give you what you want?
If not quite, please change your query to SQL View and copy and paste the SQL code of the query here so we can see it.

[BatmanMR287]

SELECT PolicyUpdates.PolicyNum, Max(PolicyUpdates.DateRev) AS MaxOfPolicyRev, Policies.PolicyName
FROM Policies INNER JOIN PolicyUpdates ON Policies.PolicyNum = PolicyUpdates.PolicyNum
WHERE (((Policies.Deleted)=False))
GROUP BY PolicyUpdates.PolicyNum, Policies.PolicyName
HAVING (((Max(PolicyUpdates.DateRev))>DateAdd("yyyy",-5,Date())));

This is mostly working. All seems to work except selecting updates that are 5 years in the past.

[BatmanMR287]

Ahhh, the > was backwards. Works perfect. Thank you very much JoeM!

[JoeM]

Glad we got it to work out for you!