Need last purchased Price by Part Number from query

[Dave14867]

Hello all,

This has been beating me up for a while now. I want to add some info to a report for the last purchased price for each item in the report. I cannot get a query that will give me 1 line per part number with the last price paid. I have the "Transactions Table" which holds the data for parts coming in, being consumed and going out. What I want is a query that comes up with 1102 part numbers and a value in the UnitPrice field for most of them, ( not every PN will have a UnitPrice because they get used in another upper level Part).

I have included a file with what I believe are the tables needed, but no reports, forms, etc for confidential reasons. The Pic below shows what I get returned to give you and Idea of what I am trying to achieve but this should have far more parts with prices than it does.






MRP 6-19-19 In Devlopment.zip

I hope I have explained what I am trying to achieve good enough. If not I will try to be clearer based on responses.

Thanks in advance

Dave

[June7]

Options:

1. TOP N query to pull the 'latest' record for each part, review http://allenbrowne.com/subquery-01.html#TopN

2. complex domain aggregate function expression, review https://www.accessforums.net/showthread.php?t=77202

[Dave14867]

June7,

Can such a query be constructed in the Query Builder? I am not strong at all in SQL.

Thanks

Dave

[June7]

I usually end up building in SQLView for option 1.

Can certainly build expressions using domain aggregates in DesignView.