Access 2016 Query to find latest inventory transaction in table.

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!
Barney EvansIT System AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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, Developing Solutions LLCCommented:
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 AdministratorAuthor 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
Determine the Perfect Price for Your IT Services

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

Dale FyeOwner, Developing Solutions LLCCommented:
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 AdministratorAuthor Commented:
Now I'm getting "Your query does not include the specified expression "item_no" as part of an aggregate function.
Dale FyeOwner, Developing Solutions LLCCommented:
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)
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Barney EvansIT System AdministratorAuthor 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 AdministratorAuthor 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, Developing Solutions LLCCommented:
Glad to help, Barney.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.