query to find most recent record

I have a table tracking serial numbered parts by location.  There are many records per serial number.  I need my report to only show the most recent entry for a serial number.  Currently, when the report opens the user is prompted for the serial number.  Then all the records for that serial number are displayed.  I just need the most recent.

Thanks,
Brooks
gbnortonAsked:
Who is Participating?
 
hieloConnect With a Mentor Commented:
try:
SELECT TOP 1 [Current Location].SerialNumber, [Current Location].[Date Moved], TubeData.Date_Received, TubeData.Customer, TubeData.TubeType, TubeData.SalesOrder, TubeData.CustomerPO, TubeData.VmiPartNumber, TubeData.Status, [Current Location].Area, [Current Location].[Date Moved]
FROM TubeData INNER JOIN [Current Location] ON TubeData.SerialNumber=[Current Location].SerialNumber
WHERE ((([Current Location].SerialNumber)=[Find Serial Number:]))
ORDER BY [Current Location].[Date Moved] DESC

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
select serialnumber, max([datefield])
from tableX
group by serialnumber
0
 
hieloCommented:
>> I just need the most recent.
Is there a field that reflects when it was added - ex: lastUpdate?
Yes:
SELECT TOP 1 [location] FROM Table WHERE [serialNumber]='XXX' ORDER BY [lastUpdate] DESC


Or an auto-number field - ex: ID?
SELECT TOP 1 [location] FROM Table WHERE [serialNumber]='XXX' ORDER BY [ID] DESC
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
gbnortonAuthor Commented:
capricorn1,
Here is my SQL statement with an attempt at your recommendation;
SELECT [Current Location].SerialNumber, max([Current Location].[Date Moved]), TubeData.Date_Received, TubeData.Customer, TubeData.TubeType, TubeData.SalesOrder, TubeData.CustomerPO, TubeData.VmiPartNumber, TubeData.Status, [Current Location].Area, [Current Location].[Date Moved]
FROM TubeData INNER JOIN [Current Location] ON TubeData.SerialNumber=[Current Location].SerialNumber
WHERE ((([Current Location].SerialNumber)=[Find Serial Number:]));

It generates the error "You tried to execute a query that does not include the speicified expression 'SerialNumber" as part of an agregate expression
0
 
gbnortonAuthor Commented:
helio,
How do I modify my SQL statement with your code?
SELECT [Current Location].SerialNumber, TubeData.Date_Received, TubeData.Customer, TubeData.TubeType, TubeData.SalesOrder, TubeData.CustomerPO, TubeData.VmiPartNumber, TubeData.Status, [Current Location].Area, [Current Location].[Date Moved]
FROM TubeData INNER JOIN [Current Location] ON TubeData.SerialNumber=[Current Location].SerialNumber
WHERE ((([Current Location].SerialNumber)=[Find Serial Number:]));

Thanks,
Brooks
0
 
gbnortonAuthor Commented:
Perfect!  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.