Want to Return Last Transaction Record Only

[songwritingguy]

Greetings,

Sorry...this is a repost - I originally posted it under Forms by mistake.
I have a locations in my warehouse defined in the database and also transactions that are logged each time an item is 'picked' from one of those locations. I'm trying to create a query that will return ONLY the last 'pick' or transaction from each location. I tried using 'Last' under the total selection with my date/time/stamp, and it does not return the last activity. What am I doing wrong?
It appears as though my problem may be due to the date format but I'm not sure.

Thanks for the help!

Here's my query:

SELECT dbo_ITEM_LOCATION_ASSIGNMENT.warehouse, dbo_ITEM_LOCATION_ASSIGNMENT.ITEM, dbo_TRANSACTION_HISTORY.QUANTITY, dbo_ITEM_LOCATION_ASSIGNMENT.ALLOCATION_LOC, dbo_TRANSACTION_HISTORY.WORK_GROUP, Last(dbo_TRANSACTION_HISTORY.DATE_TIME_STAMP) AS LastOfDATE_TIME_STAMP
FROM dbo_ITEM_LOCATION_ASSIGNMENT INNER JOIN dbo_TRANSACTION_HISTORY ON dbo_ITEM_LOCATION_ASSIGNMENT.ALLOCATION_LOC = dbo_TRANSACTION_HISTORY.LOCATION
GROUP BY dbo_ITEM_LOCATION_ASSIGNMENT.warehouse, dbo_ITEM_LOCATION_ASSIGNMENT.ITEM, dbo_TRANSACTION_HISTORY.QUANTITY, dbo_ITEM_LOCATION_ASSIGNMENT.ALLOCATION_LOC, dbo_TRANSACTION_HISTORY.WORK_GROUP
HAVING (((dbo_ITEM_LOCATION_ASSIGNMENT.warehouse)="NASH549") AND ((dbo_ITEM_LOCATION_ASSIGNMENT.ITEM) Like [Enter Item]) AND ((dbo_TRANSACTION_HISTORY.WORK_GROUP)="picking"))
ORDER BY dbo_ITEM_LOCATION_ASSIGNMENT.warehouse, dbo_ITEM_LOCATION_ASSIGNMENT.ITEM, Last(dbo_TRANSACTION_HISTORY.DATE_TIME_STAMP) DESC;

 

[Golom]

Try

SELECT A.warehouse, A.ITEM, H.QUANTITY, A.ALLOCATION_LOC, H.WORK_GROUP, H.DATE_TIME_STAMP

FROM dbo_ITEM_LOCATION_ASSIGNMENT As A INNER JOIN dbo_TRANSACTION_HISTORY As H ON A.ALLOCATION_LOC = H.LOCATION

WHERE A.warehouse ="NASH549"  AND A.ITEM Like [Enter Item] 
 AND H.WORK_GROUP ="picking"
 AND H.Date_Time_Stamp = 
     (Select MAX(Date_Time_Stamp) 
      From dbo_TRANSACTION_HISTORY As T
      Where T.Location = A.ALLOCATION_DOC)

GROUP BY A.warehouse, A.ITEM, H.QUANTITY, A.ALLOCATION_LOC, H.WORK_GROUP

ORDER BY 1, 2, 6 DESC;

 

[lespaul]

Try this:

SELECT LA.warehouse, LA.ITEM, TH.QUANTITY, LA.ALLOCATION_LOC, TH.WORK_GROUP, TH.DATE_TIME_STAMP
FROM dbo_ITEM_LOCATION_ASSIGNMENT LA 
INNER JOIN dbo_TRANSACTION_HISTORY TH ON LA.ALLOCATION_LOC = TH.LOCATION
WHERE LA.warehouse="NASH549" AND LA.ITEM Like [Enter Item] AND TH.WORK_GROUP="picking" AND TH.DATE_TIME_STAMP = (SELECT MAX(DATE_TIME_STAMP) FROM dbo_TRANSACTION_HISTORY TH2 WHERE TH2.LOCATION = LA.ALLOCATION_LOC)
ORDER BY LA.warehouse, LA.ITEM, TH.DATE_TIME_STAMP DESC;

notice that by using an ALIAS for your tables, the SQL is much easier to read!!


Leslie

 

[PHV]

What about this ?
SELECT A.warehouse, A.ITEM, H.QUANTITY, A.ALLOCATION_LOC, H.WORK_GROUP, H.DATE_TIME_STAMP
FROM (dbo_ITEM_LOCATION_ASSIGNMENT A INNER JOIN dbo_TRANSACTION_HISTORY H ON A.ALLOCATION_LOC = H.LOCATION)
INNER JOIN (SELECT LOCATION, Max(DATE_TIME_STAMP) As MaxDate FROM dbo_TRANSACTION_HISTORY WHERE WORK_GROUP='picking' GROUP BY LOCATION) M ON H.LOCATION = M.LOCATION And H.DATE_TIME_STAMP = M.MaxDate
WHERE A.warehouse='NASH549' AND A.ITEM Like [Enter Item] AND H.WORK_GROUP='picking'
ORDER BY A.warehouse, A.ITEM, H.DATE_TIME_STAMP DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244