Max Value

[spyldbrat]

Hello,



Based on the table below, I need to create a query that displays only the line with most recent data for each person listed. I would need the output of the query to only display the row with most recent date for each employee. In this case, my query would only display rows 2, 4 and 6. When I use the max function in the status date, it still displays both lines since the status is different on each line.

Row Num	First Name	Last Name	DOB	SSN	Status	Status Date
1	John	Doe	07/04/1992	111-11-1111	Registered	04/27/16
2	John	Doe	07/04/1992	111-11-1111	Terminated	04/04/17
3	Mike	Smith	10/28/1979	222-22-2222	Terminated	01/18/16
4	Mike	Smith	10/28/1979	222-22-2222	Renewed	01/18/17
5	Jane	Arnold	04/10/1993	333-33-3333	Registered	11/11/16
6	Jane	Arnold	04/10/1993	333-33-3333	Renewed	07/02/17

[pbaldy]

This should help:

http://www.baldyweb.com/LastValue.htm

[Bulzie]

As Paul's example describes, your first query gets you the max record for each group(this query must include the unique value for each record), then you use that in 2nd query to link back to original table to get all the fields for only those max records. Will the Status dates always match the order of the Row Num values? If so your first query could just use SSN(GroupBy) and then RowNum(Max) to get the max record for each person. Remember that the order of the fields columns when using Totals/GroupBy will dictate how the data is grouped or totaled, etc.

[spyldbrat]

Perhaps I should have said my skills with Access are very limited. I do know how to join two queries and I know I am supposed to go to the SQL and modify what exists to match the the link but I am not succeeding??? :/

[pbaldy]

In design view you click/drag to draw a line between the appropriate fields in the query and table.

[spyldbrat]

Paul, I am assuming you are talking joining the two queries? I am still stuck on how to modify/create the first Query so that I can ultimately join it with the second one.

[Bulzie]

Click on Create, Query Design at top ribbon, add your table, drag the 2 fields into the columns SSN first then RowNum. Click on the Totals button in ribbon at top. In that row in the columns under each field, for SNN you should have GroupBy, for RowNum you should have Max. Now run that and see if it gives you the last record for each member. If so, save it as something...qryMemberMaxRecord maybe.

Now close that query and create a 2nd query, add your table again and add this new query qryMemberMaxRecord also. Select the RowNum from the table and drag the line to the RowNum in the query. It will create the link between the 2 datasets. Now add all the fields from your table into the columns of this query. Run it and see if it gives you the max records and all the data fields. If so, save that and you are done.