Using the Max function to get last record

[ChuckW]

Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W

 

[Dale Fye]

Another way to accomplish this, which I've found to be quicker than
Tom's method when you are working with large data sets, or when you
need the most recent transaction for every job number in a single
query, is as follows. This method computes the most recent job
activity for each job number in a single operation, then joins it to
your table.

SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T
INNER JOIN
[SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber]. as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent


--
HTH

Dale Fye


Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W

 

[Michel Walsh]

Hi,



The [ ]. syntax is not documented and not supported, but allow to use virtual tables in
Access 97 (Jet 3.51). With Jet 4.0, use the standard syntax:


SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T INNER JOIN
(SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber) as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent



Hoping it may help,
Vanderghast, Access MVP

If you're writing for the Jet database engine, Dale's suggestion is
undoubtably correct. But this uses some non-standard (that is non-SQL
standard) syntax and isn't the way to do it for MSDE.

I like to stick with things that work both ways, but that certainly may not
be Dale's goal. And his point is certainly valid and worthy of
implementation as long as it's for Jet and going to stay that way.

Dale, what's up with the thing that looks like a period after the closing
square bracket? If you drop that and change the square brackets to parens,
does it still work? If so, then you would have a syntax that's portable to
MSDE as well. If we can show a syntax that works for both engines, along
with having a speed advantage when used with Jet (but probably no advantage
with MSDE) then that would be the best advice, in my opinion.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Another way to accomplish this, which I've found to be quicker than
Tom's method when you are working with large data sets, or when you
need the most recent transaction for every job number in a single
query, is as follows. This method computes the most recent job
activity for each job number in a single operation, then joins it to
your table.

SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T
INNER JOIN
[SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber]. as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent


--
HTH

Dale Fye


Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W

 

[Tom Ellison]

Thanks, Michel. I've seen that syntax several times before. You say it is
"not documented." But is there some information on it somewhere?

For my purposes, what is important is that it is "not portable". As in, it
works with Access Jet but not Access MSDE.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi,



The [ ]. syntax is not documented and not supported, but allow to use virtual tables in
Access 97 (Jet 3.51). With Jet 4.0, use the standard syntax:


SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T INNER JOIN
(SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber) as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent



Hoping it may help,
Vanderghast, Access MVP

If you're writing for the Jet database engine, Dale's suggestion is
undoubtably correct. But this uses some non-standard (that is non-SQL
standard) syntax and isn't the way to do it for MSDE.

I like to stick with things that work both ways, but that certainly may not
be Dale's goal. And his point is certainly valid and worthy of
implementation as long as it's for Jet and going to stay that way.

Dale, what's up with the thing that looks like a period after the closing
square bracket? If you drop that and change the square brackets to parens,
does it still work? If so, then you would have a syntax that's portable to
MSDE as well. If we can show a syntax that works for both engines, along
with having a speed advantage when used with Jet (but probably no advantage
with MSDE) then that would be the best advice, in my opinion.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Another way to accomplish this, which I've found to be quicker than
Tom's method when you are working with large data sets, or when you
need the most recent transaction for every job number in a single
query, is as follows. This method computes the most recent job
activity for each job number in a single operation, then joins it to
your table.

SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T
INNER JOIN
[SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber]. as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent


--
HTH

Dale Fye


Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W

 

[Dale Fye]

Tom,

As mentioned by Michel, it is the only way to get Access 97 to
recognize a subquery that returns more than one field. If you type it
into 2K, and then execute the query, Access will convert it to the
standard syntax. Since Chuck didn't indicate which version of Access
he was using, I put it in the format I knew would work for both
versions.

Other than that, was there something else that you identified as
non-standard?

--
HTH

Dale Fye


If you're writing for the Jet database engine, Dale's suggestion is
undoubtably correct. But this uses some non-standard (that is non-SQL
standard) syntax and isn't the way to do it for MSDE.

I like to stick with things that work both ways, but that certainly
may not
be Dale's goal. And his point is certainly valid and worthy of
implementation as long as it's for Jet and going to stay that way.

Dale, what's up with the thing that looks like a period after the
closing
square bracket? If you drop that and change the square brackets to
parens,
does it still work? If so, then you would have a syntax that's
portable to
MSDE as well. If we can show a syntax that works for both engines,
along
with having a speed advantage when used with Jet (but probably no
advantage
with MSDE) then that would be the best advice, in my opinion.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Another way to accomplish this, which I've found to be quicker than
Tom's method when you are working with large data sets, or when you
need the most recent transaction for every job number in a single
query, is as follows. This method computes the most recent job
activity for each job number in a single operation, then joins it to
your table.

SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T
INNER JOIN
[SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber]. as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent


--
HTH

Dale Fye


Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W

 

[Michel Walsh]

Hi,


The []. syntax seems to be an internal "parsed / half compiled" syntax for storage of the SQL text
that may be (that is probably a bug) displayed, mainly if you do not give an alias to the virtual
table. Seeing that, John L. Viescas has been among the first one to use it "back in Access 97", to
report that this syntax worked there too, while the standard syntax does not, in Access 97... One
of the possible reason it was not officially released by Microsoft, in Access 97, was discovered
later, it does not accept [ ] in it, so, it is useless if you have a illegal table name or field
name, since you would have to use [ ] around it. Other limitations have been found later on
(problems that affect somehow Access 2000 too). It seems, for Access97, that is was an "attempt"
that did not fully pass the assurance quality, possibly an explanation about why that "curious"
syntax was left in the product, since then... All that, sure, is not official, evidently, just,
somehow, some "conclusions" derived from observations.

Vanderghast, Access MVP

Thanks, Michel. I've seen that syntax several times before. You say it is
"not documented." But is there some information on it somewhere?

For my purposes, what is important is that it is "not portable". As in, it
works with Access Jet but not Access MSDE.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi,



The [ ]. syntax is not documented and not supported, but allow to use virtual tables in
Access 97 (Jet 3.51). With Jet 4.0, use the standard syntax:


SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T INNER JOIN
(SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber) as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent



Hoping it may help,
Vanderghast, Access MVP

If you're writing for the Jet database engine, Dale's suggestion is
undoubtably correct. But this uses some non-standard (that is non-SQL
standard) syntax and isn't the way to do it for MSDE.

I like to stick with things that work both ways, but that certainly may not
be Dale's goal. And his point is certainly valid and worthy of
implementation as long as it's for Jet and going to stay that way.

Dale, what's up with the thing that looks like a period after the closing
square bracket? If you drop that and change the square brackets to parens,
does it still work? If so, then you would have a syntax that's portable to
MSDE as well. If we can show a syntax that works for both engines, along
with having a speed advantage when used with Jet (but probably no advantage
with MSDE) then that would be the best advice, in my opinion.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Another way to accomplish this, which I've found to be quicker than
Tom's method when you are working with large data sets, or when you
need the most recent transaction for every job number in a single
query, is as follows. This method computes the most recent job
activity for each job number in a single operation, then joins it to
your table.

SELECT JobNumber, LastActionDate, LastActionHistory, Hours
FROM YourTable T
INNER JOIN
[SELECT JobNumber, MAX(LastActionDate) as MostRecent
FROM YourTable
GROUP BY JobNumber]. as T1
ON T.JobNumber = T1.JobNumber
AND T.LastActionDate = T1.MostRecent


--
HTH

Dale Fye


Hi,

I have a table called LastActionHistory. It contains the
following fields: JobNumber, LastActionDate,
LastActionHistory and Hours. It is used to keep track of
the status of jobs. There is often several entries for
each job number. For instance, we may have:

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/1/03 Designed Layout 4
100 6/10/03 Met with Cust 2
100 6/15/03 Finished Job 3

What I want to do is to write a query that will only get
the last record chronologically per job. So, I would
only get

JOBNUMBER LASTACTIONDATE LASTACTIONHIST HOURS
100 6/15/03 Finished Job 3

I think I would use the Max function but am not sure.

Thanks,

Chuck W