Find 2 most recent dates

[cmiley]

I am trying to create a query that will return the 2 most recent dates. I currently have my query returning the Max & Min but instead of the Min date I want it to return the next most recent date. Here is what I have so far

SELECT qryCodeRestrictions.LastName, qryCodeRestrictions.FirstName, qryCodeRestrictions.DateofBirth, qryCodeRestrictions.ProcedureCode, Max(qryCodeRestrictions.DateofService) AS MaxOfDateofService, Min(qryCodeRestrictions.DateofService) AS MinOfDateofService, DateDiff("m",[MinOfDateOfService],[MaxOfDateOfService]) AS DaysBetweenServices, qryCodeRestrictions.Provider
FROM qryCodeRestrictions
GROUP BY qryCodeRestrictions.LastName, qryCodeRestrictions.FirstName, qryCodeRestrictions.DateofBirth, qryCodeRestrictions.ProcedureCode, qryCodeRestrictions.Provider

[alansidman]

What about using the Top 2 Values. Look at this video for an idea on how to do this. http://www.datapigtechnologies.com/f...topvalues.html

[cmiley]

Thanks but not quiet what I am looking for. This query is basically finding duplicates of certain procedures of multiple clients and I need the two most recent dates for each client. Using the Top 2 values only gives me the first two clients

[aytee111]

Query 1: find max date by customer
Query 2: Query 1 joined to table, max date by customer where date is not equal to query 1 date.

[cmiley]

Thanks for the response. This keeps asking me to enter a parameter value

[orange]

cmiley,

Better show us the query code that's asking for a parameter.
Sounds like there an issue with your code.

Also show sample table records.

[aytee111]

Parameter usually means that there is a field with square brackets ([ ]) that is unidentified, it isn't in one of the source tables/queries, or on a form that is open.

[orange]

As aytee111 says Or a misspelled field

[cmiley]

Took care of the parameter issue but now I get a syntax error. Here is the SQL code

SELECT qryCodeRestrictions.LastName, qryCodeRestrictions.FirstName, qryCodeRestrictions.DateofBirth, qryCodeRestrictions.ProcedureCode, qry6MonthCodesMax.LastDateofService, Max(qryCodeRestrictions.DateofService) AS PreviousDateOfService, qryCodeRestrictions.Provider
FROM qryCodeRestrictions, qry6MonthCodesMax
GROUP BY qryCodeRestrictions.LastName, qryCodeRestrictions.FirstName, qryCodeRestrictions.DateofBirth, qryCodeRestrictions.ProcedureCode, qry6MonthCodesMax.LastDateofService, qryCodeRestrictions.Provider
WHERE <>[qry6MonthCodesMax].[LastDateOfService]
HAVING (((qryCodeRestrictions.ProcedureCode)="D0120" Or (qryCodeRestrictions.ProcedureCode)="D1110" Or (qryCodeRestrictions.ProcedureCode)="D2910" Or (qryCodeRestrictions.ProcedureCode)="D2920" Or (qryCodeRestrictions.ProcedureCode)="D0140"));


Here is what I want the table to look like

[aytee111]

What's this mean: WHERE <>

[cmiley]

not equal to

[orange]

Research Select TOP to get the 2 most recent

http://www.blueclaw-db.com/accessque...select_top.htm

http://www.w3schools.com/sql/sql_top.asp

Good luck.

This just came in while typing
WHERE <>[qry6MonthCodesMax].[LastDateOfService] ???? you need an expression not equal to what???

[cmiley]

Been there done that. not what I am looking for. That is only giving me the top two records of the whole table and I need the top two records for each customer

[orange]

Review this https://www.accessforums.net/showthr...513#post334513

Look a the TOP N article here. http://allenbrowne.com/subquery-01.html

Also we have no idea what your query is doing.

It may be a better strategy to find the top 2 things you're looking for and then add that as a subquery.

I don't understand the use MaxDateofService and BirthDate and ProcedureCode.

Another thing you might consider, if you select the top 1 of something, and then select the Top 1... where the value is not in (select the top 1...) you will get the second top record for the criteria.

That is, if X is the Top 1 record, then selecting the Top 1 record from the table where the value is not in (X), you will get the second highest/latest record.

[InsuranceGuy]

Here's a demo of how to get the last two DOS in Access DBs with the criteria you included in the SQL sample.

Cheers,


Jeff

Last Two Demo DB.mdb