Query-list last date only

[Karen]

I have a dB with all the activity for each item by serial
no. and date. I want to run a query that will show me
every serial no. but only the last (by date) entry.

Serial No. / Date / event / location

right now I am getting 20,000 lines in the report. I want
the last date of each serial no.

Thank you,
Karen

 

[Tom Ellison]

Dear Karen:

SELECT [Serial No.], Max([Date]) AS LastDate
FROM YourTable
GROUP BY [Serial No.]

In the above, make sure the column names are correct and change YourTable to
the name of your table.

 

[John Spencer (MVP)]

Tom and Karen,
I read Karen's request as her wanting to get Event and Location also.

SELECT [Serial no.], [Date], Event, Location
FROM TableName
WHERE [Date] = (SELECT Max(T.Date) as MaxDate
FROM TableName as T
WHERE [T].[Serial No.] = TableName.[Serial no.])

Which also might work as the following (and be faster)

SELECT A.[Serial no.], A.[Date], A.Event, A.Location
FROM TableName As A INNER JOIN (SELECT T.[Serial no.], Max(T.Date) as MaxDate
FROM TableName as T
GROUP By T.[Serial No.]) as Tmp
ON A.[Serial no.] = Tmp.[Serial no.]

It all depends on the version of Access and whether or not you are using MSDE or
Jet as the database. AND whether or not I got the syntax right.

Dear Karen:

SELECT [Serial No.], Max([Date]) AS LastDate
FROM YourTable
GROUP BY [Serial No.]

In the above, make sure the column names are correct and change YourTable to
the name of your table.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I have a dB with all the activity for each item by serial
no. and date. I want to run a query that will show me
every serial no. but only the last (by date) entry.

Serial No. / Date / event / location

right now I am getting 20,000 lines in the report. I want
the last date of each serial no.

Thank you,
Karen

 

[Tom Ellison]

I suspected that as well, but in this case I chose the option to keep it
simple and risk having her post back that she needed more. What you posted
here is pretty much what I had in mind if she did want those other columns.

It's probably a good thing that you gave both subquery criteiron and
subquery inner join methods. As you indicate, the inner join is probably
faster for Jet when there is a large enough table to test it.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Tom and Karen,
I read Karen's request as her wanting to get Event and Location also.

SELECT [Serial no.], [Date], Event, Location
FROM TableName
WHERE [Date] = (SELECT Max(T.Date) as MaxDate
FROM TableName as T
WHERE [T].[Serial No.] = TableName.[Serial no.])

Which also might work as the following (and be faster)

SELECT A.[Serial no.], A.[Date], A.Event, A.Location
FROM TableName As A INNER JOIN (SELECT T.[Serial no.], Max(T.Date) as MaxDate
FROM TableName as T
GROUP By T.[Serial No.]) as Tmp
ON A.[Serial no.] = Tmp.[Serial no.]

It all depends on the version of Access and whether or not you are using MSDE or
Jet as the database. AND whether or not I got the syntax right.

Dear Karen:

SELECT [Serial No.], Max([Date]) AS LastDate
FROM YourTable
GROUP BY [Serial No.]

In the above, make sure the column names are correct and change YourTable to
the name of your table.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I have a dB with all the activity for each item by serial
no. and date. I want to run a query that will show me
every serial no. but only the last (by date) entry.

Serial No. / Date / event / location

right now I am getting 20,000 lines in the report. I want
the last date of each serial no.

Thank you,
Karen