Want To Return Last Transaction Record Only

[songwritingguy]

Greetings,

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?

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;

 

[BobStubbs]

I have had a look at a query, and some sample data, and found some SQL syntax which may help you:

SELECT TOP 1 tblTest1.MyNumber
FROM tblTest1
ORDER BY tblTest1.MyNumber DESC;

This code was created by:

-- setting up a test table, tblTest1, which includes a field called MyNumber
-- creating a query to list this field
-- setting the Sort option, in Design View, to 'Descending'
-- switching to SQL view
-- Adding the TOP 1 code to the SELECT keyword.

The result ... when I run the query, the single record with the highest value in 'MyNumber' is returned.

Assuming that your last 'pick' will be the record with the highest (i.e. most recent) date and time stamp, I think you could modify your code to include the 'TOP 1' command.

There is some good information on this in Access 2000 help - search under TopValues or Top. NB the help text states this about the field to be checked for the highest value:

'The field you want to display top values for should be the leftmost field that has the Sort box selected in the query design grid. An ascending sort returns the bottommost records, and a descending sort returns the topmost records.'

I hope that this helps.

Bob Stubbs

 

[songwritingguy]

Thanks Bob!
I tried the 'TOP1' filter and also 'descending'. It appears as though this may work, but I think my date format is skewing the results. It appears to be choosing the last date/time record based on the month value which is first in the data.

Does this sound correct? Or am I missing something else?

Drew

 

[PHV]

You may try 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

 

[traingamer]

Use Max(DateTimeStamp) in your select, but not in the group by.

That being said, you must use an aggregate function for EACH field in the query. I.e. GROUP BY, MAX, FIRST, LAST, etc.

To get the desired results here may require a subquery.

traingamer

 

[traingamer]

or see the better answers in thread701-1011115

traingamer

 

[BobStubbs]

If your date format is non-standard, you may have to modify the SELECT statement like this:

SELECT TOP 1 format$(tblTest1.MyDate,"yyyymmddhhnn") etc

This worked when I tested it - it forces the dates into a standard format which sorts correctly.

Bob Stubbs

 

[songwritingguy]

Thanks Everyone!
PH - Wow...that's some interesting stuff. Your ammended query seems to work most of the time, but I noticed that sometimes the results are not always totally correct. Could be something I'm doing wrong....

I'll keep plugging away here.

Thanks again.

 

[PHV]

Probably more correct results with 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 T.LOCATION, Max(T.DATE_TIME_STAMP) As MaxDate
FROM dbo_TRANSACTION_HISTORY T INNER JOIN dbo_ITEM_LOCATION_ASSIGNMENT I
ON T.LOCATION = I.ALLOCATION_LOC
WHERE I.warehouse='NASH549' AND I.ITEM Like [Enter Item] AND T.WORK_GROUP='picking'
GROUP BY T.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