MS access - Most recent Date

P

pretty gal

I need the syntax in SQL (or Criteria) to find the most recent date in
table. table contains IssueID, ProgressLog, and ProgressUpdateDate. I a
trying to get most recent ProgressUpdateDate inthe table. IssueId ca
have more than one progressLog. I need to get most recen
progressupdatedate and progresslog in the table.

thanks in advance

Pretty Ga
 
M

Michel Walsh

Hi,


SELECT MAX(ProgressUpdateDate) FROM myTable


should do. If you need the associated fields that have this date, a possible
solution is


SELECT *
FROM myTable
WHERE ProgressUpdateDate=(SELECT MAX(ProgressUpdateDate) FROM myTable)


but if there are many records with the said date, you will get all those.


If you want the max date for EACH progressLog values, you can take a look at
http://www.mvps.org/access/queries/qry0020.htm, taking one of the 4 methods
presented there.



Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer

If you need the most recent date for each IssueID and progressLog and the
ProgressUpdateDate then

SELECT IssueId, ProgressLog, ProgressUpdateDate
FROM YourTable
WHERE ProgressUpdateDate =
(Select Max(ProgressUpDateDate)
FROM YourTable as T
WHERE T.IssueId =YourTable.IssueID)

If you need only the latest record(s) - Any ties will be shown
SELECT IssueId, ProgressLog, ProgressUpdateDate
FROM YourTable
WHERE ProgressUpdateDate =
(Select Max(ProgressUpDateDate)
FROM YourTable as T)

If you just need the date itself
Select Max(ProgressUpDateDate) as Latest FROM YourTable
 
P

pretty gal

SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId);


how can i include all the null fields? The syndex above gets only th
records have a ProgressUpdateDate. Some of the records i dont hav
date.
:(

Thanks in Advanc
 
M

Michel Walsh

Hi,


add, to the WHERE clause

OR ProgressUpdateDate IS NULL


to get:


SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId)
OR ProgressUpdateDate IS NULL



Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer

SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId) OR ProgressUPdateDate is Null
Or NOT Exists (SELECT *
FROM tblProgress as R
WHERE R.ProgressUPdateDate is not Null
AND R.IssueId=Q.IssueId)

Or if you just want all the null dates
SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId) OR ProgressUPdateDate is Null
 
P

pretty gal

SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId)
Or NOT Exists (SELECT *
FROM tblProgress as R
WHERE R.ProgressUPdateDate is not Null
AND R.IssueId=Q.IssueId)


Prett
 
J

John Vinson

SELECT Q.IssueId, Q.ProgressLog, Q.ProgressUPdateDate
FROM tblProgressLog AS Q
WHERE ProgressUPdateDate= (SELECT Max(T.ProgressUPdateDate)
FROM tblProgressLog As T
WHERE T.IssueId=Q.IssueId);


how can i include all the null fields? The syndex above gets only the
records have a ProgressUpdateDate. Some of the records i dont have
date.
:(

Thanks in Advance
Click to expand...

So if you have 50 records with various ProgressUpdateDates and 40 more
with NULL ProgressUpdateDates, which record do you want to see? The
most recent ProgressUpdateDate? An arbitrary one of the NULL dates?
All 40 NULL dates?

A NULL date will not and cannot be "the latest" date!

John W. Vinson[MVP]
 
Top