Query question

[Guest]

How do you query an item that has several different dates (records) to only
come up with the latest date for an item?

 

[Guest]

Something like

SELECT [M1].*
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 1 M2.[DateFieldName]
FROM [Table Name] as M2
WHERE M2.[Item Number] =M1.[Item Number]
ORDER BY M2.[DateFieldName] Desc)

 

[Guest]

Thank you for your immediate response; however, I am only a beginner/novice
in the world of creating databases. If I'm not mistaken, the written code
you sent me is for a SQL database which is way above my head. I apologize for
not being more specific. What I'm looking for is a way under the query
section in the criteria area to form an expression that will allow me to
withdraw the latest dates from several records with the same serial number.
Any help you can offer will be greatly appreciated.

Something like

SELECT [M1].*
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 1 M2.[DateFieldName]
FROM [Table Name] as M2
WHERE M2.[Item Number] =M1.[Item Number]
ORDER BY M2.[DateFieldName] Desc)

--
\\// Live Long and Prosper \\//
BS"D

How do you query an item that has several different dates (records) to only
come up with the latest date for an item?

 

[Douglas J. Steele]

When you're in the Query Designer in Design mode, you can choose SQL View
from the View menu. Then you can use the SQL statement that Ofer gave you.

It's far easier for us to give solutions that way then to try and outline
the series of steps you'd have to undertake to build the equivalent query
through the graphical interface.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for your immediate response; however, I am only a
beginner/novice
in the world of creating databases. If I'm not mistaken, the written code
you sent me is for a SQL database which is way above my head. I apologize
for
not being more specific. What I'm looking for is a way under the query
section in the criteria area to form an expression that will allow me to
withdraw the latest dates from several records with the same serial
number.
Any help you can offer will be greatly appreciated.

Something like

SELECT [M1].*
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 1 M2.[DateFieldName]
FROM [Table Name] as M2
WHERE M2.[Item Number] =M1.[Item Number]
ORDER BY M2.[DateFieldName] Desc)

--
\\// Live Long and Prosper \\//
BS"D

How do you query an item that has several different dates (records) to
only
come up with the latest date for an item?