Query with a "last" in a field

[zozew]

Hi ya'll

I got a query (from different tables) where I use the "last" option on a field (info) to get the last record in that table.. I guess it's based on the Autokey or just the last record in the table..

And that works great in my query mostly... It's just that it's not always the last record I want... You see I have a date field in the record as well and I want to get the most recent record according to that date...

Is there a way of getting the most recent record according to the date in that date field in a similar way

The table I'm talking about has three fields

Autokey : number
DateChange : date
Info: text

Any help appreciated

 

[jdraw]

I never use Last or First -- I have seen documents that say these give inconsistent results.

For last -meaning the most recent- you can use a Date field

Select info from yourTable where 
DateChange = (SELECT Max(DateChange) from yourtable )

Good luck.

 

[zozew]

How do I syntax that from the query builder in the criteria? And under what field the info field or datechange?

Sorry for nooby questions..

 

[jdraw]

To test the SQL I provided --assuming you use the proper names for your fields and table.

Copy the SQL
Open your query designer
select sql mode
paste the sql
execute the query

review the results

 

[zozew]

Ok.. That part i guessed but as I mentioned the table I mentioned is part of a larger query... I think you helped my before I'm doing the jail db... And this query is getting personal info from one table then a related table inmate info from another and security info from a related table to the inmate one... So I cant really just exchange the sql... I presume i have to somehow play around with the where in the sql... But I was hoping I could somehow rewrite what you suggested into the criteria of one of the fields... Maybe that's not possible...?

 

[jdraw]

Research MSAccess multiple table queries. There will be some helpful youtube videos.

Here's a start https://support.office.com/en-in/ar...e-tables-4a14e73a-3ecd-481c-a416-09571c7064a9

 

[zozew]

This Query on the table below

SELECT ID, Value, As_of 
FROM Table a inner join (SELECT ID, MAX(As_of) AS As_of FROM Table GROUP BY ID) b ON a.ID=b.ID AND a.As_of = b.As_of

[B]Table:[/B]
ID      Value  As_of
1173    156    20090601
1173    173    20081201
1173    307    20080901
1173    305    20080601
127     209    20090301
127     103    20081201
127     113    20080901
127     113    20080601
1271    166    20090201
1271    172    20081201
1271    170    20080901
1271    180    20080601

Makes it look like this:

[B]Result[/B]
ID      Value    As_of
1173    156      20090601
127     209      20090301
1271    166      20090201

It get the MAX for the As_of for each ID and returns that...

I have basically the same table but not as a "physical table" but as a query result:

Query:

SELECT tblClassificationHistory.inmateID, tblClassificationHistory.classificationID
FROM tblinmate INNER JOIN tblClassificationHistory ON tblinmate.inmateID = tblClassificationHistory.inmateID
GROUP BY tblClassificationHistory.inmateID, tblClassificationHistory.classificationID, tblClassificationHistory.reclassificationDate
ORDER BY tblClassificationHistory.inmateID;

Makes this Result:

inmateID	classificationID	reclassificationDate
2              3                       9/22/2015
2              4                       9/13/2015
2              8                       9/8/2015
3              8                       9/13/2015
4              4                       9/15/2015
4              8                       6/16/2015
5              3                       9/15/2015
5              4                       9/16/2015
5              8                       7/7/2015
6              8                       9/14/2015
7              7                       9/13/2015
8              3                       8/4/2015
8              8                       6/1/2015
9              3                       9/15/2015
10              6                       9/13/2015
11              3                       9/13/2015
12              3                       8/3/2015
12              4                       9/9/2015
12              6                       9/13/2015
12              8                       7/6/2015
13              8                       9/13/2015
14              8                       9/13/2015
15              4                       9/13/2015
16              4                       9/13/2015
17              8                       9/14/2015
18              8                       9/14/2015
19              3                       9/15/2015

I want to use "His" query in mine but after a few hours i cant...i presume its a subquery..but i cant for the life of me get the syntax right...

Result Im after: classificationID for Last date for each distinct inmateID

inmateID	classificationID	reclassificationDate
2		3			9/22/2015
3		8			9/13/2015
4		4			9/15/2015
5		4			9/16/2015
6		8			9/14/2015
7		7			9/13/2015
8		3			8/4/2015
9		3			9/15/2015
10		6			9/13/2015
11		3			9/13/2015
12		6			9/13/2015
13		8			9/13/2015
14		8			9/13/2015
15		4			9/13/2015
16		4			9/13/2015
17		8			9/14/2015
18		8			9/14/2015
19		3			9/15/2015

 

[zozew]

This is the answer if anybody is interested...

SELECT a.inmateID,        
              a.classificationID,        
              b.max_date 
FROM (     
 SELECT tblClassificationHistory.inmateID,             
               tblClassificationHistory.classificationID,                            
               tblClassificationHistory.reclassificationDate     
FROM tblinmate     
INNER JOIN tblClassificationHistory     
ON tblinmate.inmateID = tblClassificationHistory.inmateID  
) a 
INNER JOIN (     
   SELECT tblClassificationHistory.inmateID, 
               MAX(tblClassificationHistory.reclassificationDate) as max_date     
   FROM tblinmate     
   INNER JOIN tblClassificationHistory     
   ON tblinmate.inmateID = tblClassificationHistory.inmateID     
   GROUP BY tblClassificationHistory.inmateID   
) b 
ON a.inmateID = b.inmateID 
AND a.reclassificationDate = b.max_date 
ORDER BY a.inmateID;