Access 2016 Query to find latest inventory transaction in table.

Barney Evans
Barney Evans used Ask the Experts™
on
Hi!

I have a table of inventory transactions. I need to find the latest transaction that has a type of "S" and price higher than 0 for each part number.

Here is some sample data from the table:
item_no	trx_type	trx_qty	unit_price	trx_date
CT0603CSF-10NG	S	10	0	09/20/13
CT0603CSF-10NG	R	-4000	0	09/04/13
CT0603CSF-10NG	R	4000	0	09/04/13
CT0603CSF-10NG	S	4000	0.07	09/04/13
CT0603CSF-10NG	S	30	0	08/28/13
CT0603CSF-10NG	R	33	0	08/15/13
CT0603CSF-10NG	R	-33	0	08/15/13
CT0603CSF-10NG	R	33	0	08/15/13
CT0603CSF-10NG	R	4000	0	03/22/13
CT0603CSF-10NG	R	-4000	0	03/22/13
CT0603CSF-10NG	R	4000	0	03/22/13
CT0603CSF-10NG	S	4000	0.08	03/22/13
CT0603CSF-10NG	R	2000	0	08/29/12
CT0603CSF-10NG	S	2000	0.07	08/29/12

Open in new window


I'm been working with this and I am stuck. Here is the current query I am using:

SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
WHERE trx.unit_price <>0 AND trx.trx_type= 'S' AND trx.Trx_Date=
(Select Max(Table2.Trx_Date) From trx Table2
WHERE trx.item_no = Table2.item_no Group by Table2.item_no);

Open in new window


It works fine unless the latest transaction has a price of 0 or a type that is not "S" then I get no results.

With this data I am expecting a result of:

item_no	trx_type	trx_qty	unit_price	trx_date
CT0603CSF-10NG	S	4000	0.07	09/04/13

Open in new window


I am working in Access 2016. Any suggestions will be greatly appreciated.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try with:

SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
WHERE trx.Trx_Date IN
    (SELECT TOP 1 T.Trx_Date 
    FROM trx As T
    WHERE T.unit_price > 0 AND T.trx_type= 'S' AND T.item_no = trx.item_no
    ORDER BY trx.Trx_Date Desc) 

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Gustav,  I think your query is going to return duplicates, because your subquery only returns a date and the main table has multiple records for each date.

Barney,

I would use:
SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
INNER JOIN (
SELECT trx.Item_No, Max(trx.trx_date) as MaxDate
FROM trx
WHERE trx.trx_Type = "S"
AND trx.Unit_Price > 0
) as T2 on trx.Item_no = T2.Item_no
AND trx.trx_Date = T2.MaxDate
AND trx.trx_type = "S" 
AND trx.unit_price > 0

Open in new window

But this still doesn't guarantee there will be no duplicates if there are two records for a particular Item_no which have trx_type = "S" and Price > 0

The only way to quarantee a single record for each Item_no is if the table had an autonumber field or date/time field with date and time values to identify that "last" record of each type.
Barney EvansIT System Administrator

Author

Commented:
Hi Dale,

Your query is throwing an error in Access. "JOIN expression not supported"

If it helps the full table I will be querying does have an ID column  called "inv_trx_rec" It is an auto-number field. I didn't include in the sample data.

Barney
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Try replacing lines 10 and 11 with:

WHERE trx.trx_type = "S"
AND trx.unit_price > 0

I'll have to relook the table structure and see if I can find a better way to construct the query now that I know there is an ID column in the mix.

Dale
Barney EvansIT System Administrator

Author

Commented:
Now I'm getting "Your query does not include the specified expression "item_no" as part of an aggregate function.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
OK, lets try this:

SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
INNER JOIN (
SELECT trx.Item_No, Max(trx.trx_date) as MaxDate
FROM trx
WHERE trx.trx_Type = "S"
AND trx.Unit_Price > 0
GROUP BY Item_No
) as T2 on trx.Item_no = T2.Item_no
AND trx.trx_Date = T2.MaxDate
WHERE  trx.trx_type = "S" 
AND trx.unit_price > 0

Open in new window


Note that I added a Group By clause in the subquery to group on each item and modified the last to lines to make them part of a WHERE clause rather than part of the JOIN (which you can do in SQL , but not in Access)
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
But a better solution, using your Autonumber solution might be:

SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
INNER JOIN (
SELECT trx.Item_No, Max(trx.inv_trx_rec) as MaxRecID
FROM trx
WHERE trx.trx_Type = "S"
AND trx.Unit_Price > 0
GROUP BY Item_No
) as T2 on trx.Item_no = T2.Item_no
AND trx.inv_trx_rec = T2.MaxRec

Open in new window

But that query will not be updateable, so if you need to update those records, you might try:
SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
WHERE trx.inv_trx_rec = SELECT Max(T2.inv_trx_rec) as MaxRecID FROM trx as T2
WHERE T2.Item_no = trx.Item_no
AND T2.trx_Type = "S"
AND T2.Unit_Price > 0)

Open in new window


HTH
Dale
Barney EvansIT System Administrator

Author

Commented:
I will give those a try. Thanks for your time and effort. I've been working on this for a week and not making much headway on my own.
Barney EvansIT System Administrator

Author

Commented:
Thanks Dale,

It seems to work. I'll need to audit of the final results from the full table (28,000 records).
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Glad to help, Barney.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial