Need to find last transaction for each item in a period

[huggybear]

We have an inventory database that stores a record of each transaction using a number field called "audit_trail" which increments for each transaction. Each transaction also includes fields for the posting period which corresponds to the month (i.e. January = 1, etc.), the year, and a quantity left after the transaction.

What I need to do is find the quantity for the last transaction of a period for every item of inventory. I don't need anything fancy to look at and I can put the criteria into the QBE window. I just need help getting the query to give me only the last transaction for each item.

Bear

 

[Quehay]

This is SQL that does this with Northwind ORDERS/ORDER_DETAILS/PRODUCTS (it assumes that you want more than one year and that each month will show in the grouping due to the presence of at least one sale--if you want to be sure of presenting all months you'll need to incorporate a cross-join with a table of #'s 1-12):

[tt]SELECT DatePart("yyyy",[o].[orderdate]) AS [Year], DatePart("m",[o].[orderdate]) AS Period, rderDate AS LastInPeriod, p.ProductName, od.Quantity
FROM Products AS p INNER JOIN (orders AS o INNER JOIN [Order Details] AS od ON rderID = od.OrderID) ON p.ProductID = od.ProductID
WHERE (((rderDate)=(
SELECT Max(orders.OrderDate) AS MaxOfOrderDate
FROM orders INNER JOIN [Order Details] ON orders.OrderID = [Order Details].OrderID
WHERE ((((DatePart("yyyy",[orders].[orderdate]))=DatePart("yyyy",[o].[orderdate])))
AND ((DatePart("m",[orders].[orderdate]))=DatePart("m",[o].[orderdate])))
AND [Order Details].productID= od.productid )))
ORDER BY DatePart("yyyy",[o].[orderdate]) DESC , DatePart("m",[o].[orderdate]) DESC , 1, 2, 4;
[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[huggybear]

OK, thanks! I'm on my way out the door right now so I won't have a chance to try anything until tomorrow sometime.

Bear