Access report on last sale of SKU

I am newer at access. Can someone tell me the table that will identify the last sales transaction? I have simple access reports that identify parts by warehouse but would like to analyze slow moving inventory.

Find more posts tagged with

Comments

Legacy Contributor

INV_TRANS probably.

I'd recommend using Synoptix, Crystal, SmartViews, Q&A, or a similar report writer, all of which do not mess with the database. Access can be dangerous, as it allows writing to the database.

Legacy Contributor

Looking at the last sales transaction will show you dead inventory, but not slow-moving inventory. To analyze slow-moving inventory, you need to look at usage over time. Here's a year over year query I came up with for Access:

TRANSFORM Sum(dbo_INVENTORY_TRANS.[QTY]) AS SumOfQTY
SELECT dbo_INVENTORY_TRANS.PART_ID, dbo_PART.DESCRIPTION
FROM dbo_PART INNER JOIN dbo_INVENTORY_TRANS ON dbo_PART.ID = dbo_INVENTORY_TRANS.PART_ID
WHERE (((dbo_INVENTORY_TRANS.CUST_ORDER_ID) Is Not Null) AND ((dbo_PART.STATUS) Is Null))
GROUP BY dbo_INVENTORY_TRANS.PART_ID, dbo_PART.DESCRIPTION
ORDER BY dbo_INVENTORY_TRANS.PART_ID, Year([TRANSACTION_DATE]) DESC
PIVOT Year([TRANSACTION_DATE]);

Note that this shows customer orders only and does not include work order material issues.

Since the PART table is joined, you can also add various filters, qty_on_hand, standard costs, etc by adding them as row headers.

rm_benefit_administration_aca_training.pptx

Copyright © 2025 Infor. All rights reserved.