Top record

  • Thread starter Thread starter Deb Struble
  • Start date Start date
D

Deb Struble

I have a query housing the Serial Number, Location ID, and Move Date. The
Serial Number can have many location transactions but I need to pull only
the current location of the Serial Numbers. I was sorting by Move Date
(Desc) in order to find the current location. How do I then limit the list
to only the current location versus seeing all of them?
Thanks!
Deb
 
In the query design grid, right click the background where you place the
tables and choose Properties. Set the Top Values property to 1. The drop
down box has some sample values, but you can type in one that doesn't exist
in the drop down.
 
Deb,

Try:

SELECT *
FROM yourTable T
INNER JOIN
(SELECT T1.SerialNumber, MAX(T1.MoveDate) as LastMove
FROM yourTable T1
GROUP BY T1.SerialNumber) as T2
ON T1.SerialNumber = T2.SerialNumber
AND T1.MoveDate = T2.LastMove

The subquery that results in T2 will return the last move
date for each serial number. By joining it back to your
table on the serial number and MoveDate, you can then get
at all the other information you need regarding the most
recent location of that equipment.

HTH
Dale
 
There are two ways to solve the "MaxQuery Problem" (as I like to call it).
One is with a subquery (as Dale suggested), and the other is to create a
Totals query that shows just the SerialNumber and the Maximum Date and JOIN
that back to your original table (query).

On my website (see sig below) is a small sample database called
"MaxQueryProblem.mdb" which illustrates both. The advantage of the subquery
method is that the result set is editible. The advantage of the second
approach is that it is easier to understand and implement.
 
Dale,
Thank you so much for the information, I think this is the direction I need
to go. I typed in the following:
SELECT *
FROM tblLocationDetail T
INNER JOIN
(SELECT T1.SerialNumber, MAX(T1.MoveDate) as LastMove
FROM tblLocationDetail T1
GROUP BY T1.SerialNumber) as T2
ON T1.SerialNumber = T2.SerialNumber
AND T1.MoveDate = T2.LastMove

I receive an error: Syntax error in join operation
Any ideas as to what I am doing wrong?
Thanks!!!
 
Back
Top