Jet SQL query
Jet SQL query
Jet SQL query
Jet SQL query
Jet SQL query
Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query
Jet SQL query Jet SQL query
Jet SQL query
Go Back  Xtreme Visual Basic Talk > > > Jet SQL query


Reply
 
Thread Tools Display Modes
  #1  
Old 07-06-2016, 11:17 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Unhappy Jet SQL query


Hi Guys,

I have a table in access which I'm trying to query with Jet SQL via and ADO connection.

Each record in the table is for a serial number, the results of an assessment against it and the date it was assessed.
The primary key is a autonumber [ID] field.
A serial number can be assessed multiple times so can have multiple records, but the date and [ID] will be different.

I'm trying to pull all fields into a recordset. but only those records which are the most recent entry of each serial number. So, if the serial number 12345 occurs multiple times, I only want the most recent one (most recent date or largest [ID]).

I've tried using "SELECT [Serial Number], MAX([ID]) FROM tblAssessment GROUP BY [Serial Number]", but this doesn't allow me to retrieve all the fields for a given record.

Does anyone know of a good way to do this please?
Reply With Quote
  #2  
Old 07-08-2016, 01:17 AM
Dennis DVR's Avatar
Dennis DVRJet SQL query Dennis DVR is offline
Back in the Game

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Manila Philippines
Posts: 3,576
Default

One way to do it is something like.
Code:
SELECT * FROM tblAssessment WHERE [ID]  IN (SELECT MAX([ID]) AS MostResentID FROM tblAssessment GROUP BY [Serial Number])
Since you want to return all the fields I can't think of an easier and optimized way to do this. I haven't use Access for a very long time and I am no longer familiar with other functions available in access.
__________________
Avatar by Lebb

[Posting Guidelines] [Standards & Practices Tutorial] [Participate here effectively]
Our knowledge can only be finite, while our ignorance must necessarily be infinite. Karl Popper
Reply With Quote
  #3  
Old 07-25-2016, 05:29 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default

Thanks Dennis, that works a treat.
I'm not familiar with "MostRecentID". Does that only operate on the autonumber field or could it be applied to another numeric/date field?
Reply With Quote
  #4  
Old 07-25-2016, 08:10 PM
Kluz's Avatar
KluzJet SQL query Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

MostRecentID is an alias. It will appear as the field header in the resulting recordset. Aliasing is an important thing in SQL, it will enable you to perform multiple calculations against the same table when you alias the table among other things. The keyword "AS" is optional but should be included for clarity.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
serial, multiple, [id], date, recent, assessed, times, [serial, number], fields, records, table, query, sql, jet, record, max[id], select, largest, tblassessment, retrieve, key, primary, autonumber, field


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Jet SQL query
Jet SQL query
Jet SQL query Jet SQL query
Jet SQL query
Jet SQL query
Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query Jet SQL query
Jet SQL query
Jet SQL query
 
Jet SQL query
Jet SQL query
 
-->