I tried to put the string below into an SQL query.

It was fine until I went to save it, the it said that there was an
expression missing from:

WHERE [Date/Time] =
(SELECT MAX([Date Time]

Should there be something after the '='?


"Ken Sheridan" wrote:

Normally this would be done by means of a subquery, for which you'll need to
write the SQL for the subquery at least. Its pretty straightforward,
however, and the query would look like this (I've assumed the table is called
Pallets), so you might like to give it a try:

SELECT *
FROM Pallets AS P1
WHERE [Date/Time] =
(SELECT MAX([Date Time]
FROM Pallets AS P2
WHERE P2.[Pallet ID] = P1.[Pallet ID]);

The way it works is that the subquery finds the row with the latest (MAX)
date/time for the Pallet ID which matches the outer query's current row.
Note how the two instances of the Pallets table are distinguished by giving
them aliases P1 and P2. This type of subquery is known as a 'correlated
subquery'.

The rows returned by the outer query are restricted to those where the
date/time matches that returned by the subquery, so you get the rows with the
latest date for each pallet.

By changing the name of the table from pallets to whatever the name of your
table actually is you should be able to simply paste the above SQL into a new
query in SQL view and then open it.

It is possible to do it without writing any SQL, but you'll need two
queries. First create a 'totals' query in query design view which GROUPs BY
Pallet ID and returns the MAX Date/Time. Save this query and then create
another query in which you INNER JOIN (which is the default join type when
you create a query in design view) the original table to this query on The
Pallet ID AND Date/Time columns. In this final query include columns only
from the table, not the first query; that's purely there to identify the
latest date/time values per pallet. The rows returned will be those where
there is a match between Pallet ID and Date/Time between the table and the
first query.

Ken Sheridan
Stafford, England

"AJCB" wrote:

I have an Access table with the following fields:
Pallet ID - eg. 11234$33
Location - eg. NW
Date/Time - eg. 10/10/2007 18:00:00

What I would like to do is have a query which shows me the latest record for
each Pallet ID.

Can this be done using Expression Builder as I have not learned SQL yet?

Cheers
AJ