Access SQL Question

I have two tables in Access XP an Inventory table that has the itemnumber, quantityonhand, a purchaseorder table that has itemnumber, qunatityordered, pricepaid.  
What I need and believe it can be done is a SQL query that will pull the last purchase orders and give me a real price on the items.  This can be tricky because if I have 10 items in inventory and 3 purchase orders of different prices and different quantities for the item.  I need to only grab the last purchase orders that equate to the amount of quantityonhand in inventory then average the cost for a single item as my real cost.  
So if I had 3 purchase orders of the same item using the data below the 1st order would not even be pulled in the query, and the second order would be evaluated to be reduced
1 on 05/01/09 for a quantity of 2 at a price of $3.50
2 on 05/12/09 for a quantity of 4 at a price of $1.25
3 on 05/18/09 for a quantity of 7 at a price of $2.75  
the query would need to give me the real cost of those 10 items left in inventory which would look something like (7*2.75+3*1.25)/10 =realcost of $2.30 per item
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Mark WillsConnect With a Mentor Topic AdvisorCommented:
OK, the views were set up to pick up the value of stock at the last non-zero price. Easy enough to fix. And must admit, I didn't look too deeply into that previous explanation.

Jeffrey CoachmanMIS LiasonCommented:
You need a Date Field in the Purchase Order Table.
UprightdudeAuthor Commented:
Would it matter that the purchase order tables are sequential thus a Purchase order number being the highest would be the latest?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanMIS LiasonCommented:
I guess...

I just have never seen a Purchase Order without a date...

How far have you gotten on this yourself?
UprightdudeAuthor Commented:
There is a date on the purchaseorder table but I thought to skip that table and grab the data from the purchase order detail table which would have the item numbers that I need to query off of from the inventory.  
Jeffrey CoachmanMIS LiasonCommented:
Can you post some actual data and the expected results, based on the sample data?

In other words, so me the exact query results you are looking for.
UprightdudeAuthor Commented:
Okay this is what I have thus far
SELECT a.LocalSKU, a.ItemName, a.QOH, a.Price, (Select
Sum(b.ExpectedCost/(b.PiecesPerLot)) from PurchaseOrderDetails as b where b.LocalSKU=a.LocalSKU) as  RealCost FROM Inventory as a INNER JOIN PurchaseOrderDetails as b ON a.LocalSKU = b.LocalSKU where a.QOH>0
GROUP BY a.LocalSKU, a.ItemName, a.QOH, a.Price;

The thing that I am not sure how to do is to pull the last purchaseorderdetail records for each localsku until the piecesperlot equal or is greater than the QOH.  As this works now it pulls all of the purchaseorderdetail records for each sku and calculates it into the realcost
UprightdudeAuthor Commented:
As for more information as to what I need and what data I am attempting to get the information from.

Results desired
LocalSKU      ItemName      QOH      Price       RealCost
DW-D26451K      ItemName      17      $69.00      58.87
DW-D26453K      ItemName      12      $89.00      53.75
DW-D26441KR      ItemName      6      $0.00      26.00
DW-D26451      ItemName      5      $69.01      53.00
DW-D26441      ItemName      5      $46.53      36.00
DW-D26453      ItemName      5      $83.49      72.36
DW-D26450      ItemName      3      $66.63      57.00

DW-D26451K should pull records
all of the 1 from Record number 25 at 0 costs
all of the 12 from record number24 at 62.74 costs
only 4 of the 5 from record number 11 at 62.00
Summed up to equal 1000.88 divided by quantity on hand 17 for an realcosts of 58.87

DW-D26453K should pull records
all of the 1 from Record number 23 at 0 costs
all of the 10 from record number22 at 64.50 costs
all of the 1 from Record number 5 at 0 costs
Summed up to equal 1000.88 divided by quantity on hand 12 for an realcosts of 53.75

D26441KR should pull records
6 of the 20 from Record number 16 at 26.00 costs
Summed up to equal 416.00 divided by quantity on hand 6 for an realcosts of 26.00

DW-D26451 should pull records
all of the 5 from Record number 19 at 53.00 costs
Summed up to equal 265.00 divided by quantity on hand 5 for an realcosts of 53.00

DW-D26441 should pull records
all of the 5 from Record number 20 at 36.00 costs
Summed up to equal 180.00 divided by quantity on hand 5 for an realcosts of 36.00

DW-D26453 should pull records
all of the 3 from Record number 27 at 70.00 costs
all of the 2 from Record number 10 at 75.90 costs
Summed up to equal 361.80 divided by quantity on hand 5 for an realcosts of 72.36

DW-D26450 should pull records
all of the 2 from Record number 3 at 57.00 costs
only 1 of the 4 from Record number 2 at 57.90 costs
Summed up to equal 171.00 divided by quantity on hand 3 for an realcosts of 57.00

Data within tables 
Inventory table
LocalSKU	ItemName	QOH	Price
DW-D26451K	ItemName	17	$69.00
DW-D26453K	ItemName	12	$89.00
DW-D26441KR	ItemName	6	
DW-D26451	ItemName	5	$69.01
DW-D26441	ItemName	5	$46.53
DW-D26453	ItemName	5	$83.49
DW-D26450	ItemName	3	$66.63
DW-D26670	ItemName	0	$83.60
DW-D21002	ItemName	0	$60.79
DW-D26451KR	ItemName	0	
DW-397531-00	ItemName	0	$15.02
DW-D26441K	ItemName	0	$49.00
PurchaseOrderDetail Table
LocalSKU	Ordered	ExpectedCost
DW-D26450	1	$57.00
DW-D26450	4	$57.00
DW-D26450	2	$57.00
DW-D26453K	12	$75.00
DW-D26453K	1	$0.00
DW-D26451K	2	$62.00
DW-D26441K	1	$43.70
DW-D21002	5	$54.60
DW-D26451K	5	$62.00
DW-D26453	2	$75.90
DW-D26451K	5	$62.00
DW-397531-00	3	$12.13
DW-397531-00	1	$12.13
DW-D21002	4	$55.26
DW-D26441	5	$42.30
DW-D26441KR	20	$26.00
DW-D26451KR	20	$35.00
DW-D21002	1	$55.26
DW-D26451	5	$53.00
DW-D26441	5	$36.00
DW-D26670	2	$76.00
DW-D26453K	10	$64.50
DW-D26453K	1	$0.00
DW-D26451K	12	$62.74
DW-D26451K	1	$0.00
DW-D26670	1	$76.00
DW-D26453	3	$70.00

Open in new window

Jeffrey CoachmanMIS LiasonCommented:

The level of complexity required here exceeds my skill level.

Click the "request Attention" button and ask that the:
    Access Architecture/Design be aded to this question.

UprightdudeAuthor Commented:
Okay I Requested attention
Hopefully someone can assist me.
First comment is that the concept of 'sequential' doesn't exist for SQL tables. Rows are not returned in any guaranteed order unless an Order By clause is included in the SELECT.
In practice, most SQL db engines will return rows in entry sequence, but you never know :-)
Leaning a bit out here, I would say i can't be done in pure SQL
You will need some code to evaluate the data and perform some calculations on a subset of the data, before getting the correct reslut.
I think it could be done with the help of some VBA, but I dont know if you would be interested in that.?
UprightdudeAuthor Commented:
interesting I would think it could be done.  I know the record number can be done with a sub query to bring the row and to grab the last one is easy.  But I was hoping you experts would be able to impress me and be able to solve the issue of comparing the the last entries against the quanty on hand to pull the right amount of rows.  

Is this the general way of this forum a question will be out for less than a week receive a few comments and be closed?  
Mark WillsTopic AdvisorCommented:
Hi uprightdude,

It can depend sometimes on the nature and type of question. The experts here are volunteers and are free to choose whichever question attracts their attention. So, some questions get a lot of attention and some don't. And those that don't this week, might be the biggest next week.

Now, what you are suggesting is easy enough in pulling out the most recent purchase order, but that isn't the correct way though. It is the recursive nature of the question that Access is not so great with and where VBA is often used in these cases.

For example...

PO 3 qty 10  1.45ea
PO 4 qty 20  1.65ea

Qty on hand 20.  So pulling up the last we get a cost of 1.65ea, but looking at something more like FiFo we really should get cost at 1.55ea.

But say there has been a sale of 5 already, then we assume they come from the first lot, so the "available" stock should be reducing the first PO etc etc.

Now, what we understand of your requirement is that it is an ideal world where PO 3 adds 10 to QOH and the sale of 5 means QOH is 5 then the PO of 20 brings SOH up to 25. you want the cost to be 20 @1.65 + 5 @1.45, and in reality, the sale happens with actual goods, but the PO happens before goods are received. Either that, or QOH is only incremented when the goods are received, then it is no longer the most recent PO.

Again looks reasonable while there are only a few, but when there are several to scan, we have to choose those most recent PO's whose combined qty is greater than or equal to current QOH. And that is the tricky part (forgetting logistics of when stock is actually received into W/H).

Now, it is often the case that there is chronology and stock movements being recorded especially when establishing costs. Then you have the option of FiFo or average costing depending on when you post stock valuations to GL. For that to happen given you current setup, would possibly be best to add another table for stock movements where sales and receipts are recorded along with costs and dates that way you can more readily traverse the stock movements to get both current stock on hand and price, but again, there can sometimes be a recursive nature about that.

And I think that is also why your question is a bit difficult to answer. It can be potentially wrong to calculate stock that way without knowing a lot more about it, so becomes potentially a difficult question given the seemingly straight forward requirement of getting the last few PO's. I know I have been hovering since that message went out...

So, what do you feel about a VBA type function as per DK_User's offer ?
Thers no real problem in creating a solution, but I just dont think that a pure SQL way is possible.
As i see it you have an OrderDate we could use to identify newest Orders and thats enough to get it to work.
The way I would solve it, was using a littel bit of VBA, but as you ask for a SQL solution, you might not be interested in such a solution, hence i would be wasting my time-)
I would calculate each average price for each itemnumber, and save that value in a  seperate table. Then we could create a Report from that table, or perhaps a Form with some lookupfunction.
The think that need to be clearfied is
*what to do if you have 0 (zero) items in inventory. What value should be used to calculate the price? You cant devide by zero-)
*How would you like the result to be presented.
*How would you trigger the calculation - automatic each day, by a click on a button or even each time you enter your sales form
*What should happen if you havn't sold any yet.
Mark WillsTopic AdvisorCommented:
OK, bit quiet, so made a few assumptions and changes...

1) created an Autonumber column on PurchaseOrderDetail table - named RN - used for chronology.

2) created a view on PurchaseOrderDetail = AvailableStockCost really showing planned purchases like a running total of ordered but in reverse (ie forward)

3) created a view on Inventory and AvailableStockCost = InventoryValuationSummary - gives you what you want ie stock valuation and current unit cost (plus some other bits)

4) there is a detailed version of that view so you can "see" = InventoryValuationWorkSheet

per attached mdb - all SQL query.

There are still those other questions that really should be / could be considered from my previous posting.

I have attached an quick exampel using VBA, that you can use as inspiration if you want.
You said earlier "There is a date on the purchaseorder table but I thought to skip that.... " so i invented a PurchaseOrder table that had relation to PurchaseOrderDatail table, to use the OrderDate as sorting.
Because of some localized difference i had to replace "," with ". "in my currency format, so you might have to uncomment a sql sttring in the InsertSKU sub.
The 0 in stock situation and 0 sold are not handled.

UprightdudeAuthor Commented:
you guys have given me alot to think about and the solutions are interesting.  The Query Solution is what I would like the most however, I would have to look at it in more detail since some of its ending realcost numbers were wrong.  I think it may, not be considering an item received at $0 in its calculations.
The VBA data seems to be right on.  I will have to see how I can implement this into my existing system.  

 I do thank all of you for your insight and assistance in this I will play with both of your solutions prior to accepting a solution.  
Mark WillsTopic AdvisorCommented:
>> may, not be considering an item received at $0 in its calculations << 

It is meant to pick up the previous non-zero cost rate in those cases where a PO has no rate (that is in AvailableStockCost).

What it will not do is calculate a current stock valuation for 0 QOH, but will show you the last PO rate (that is in InventoryValuationSummary).

What we can do, is not apply that "consideration" for a zero costrate - that is if you want it to remain at zero (that is in AvailableStockCost). .

There are only two queries to look at : AvailableStockCost and InventoryValuationSummary.

The other two InventoryValuation* views were "play" things left there...
UprightdudeAuthor Commented:
Looking at the available stock cost there are multiple entries of the same sku with differing prices.  I am sure i am just not getting it and it is there.  Then in the InventoryValuationSummary looking at all the different prices you have I see that most dont match the expected output.    I am assuming that the unit cost is representitive of the realcost that I am attempting to gain.
Expected results
DW-D26451K      58.87
DW-D26453K      53.75
DW-D26441KR    26.00
DW-D26451         53.00
DW-D26441         36.00
DW-D26453          72.36
DW-D26450          57.00

Inventory valuation summary results
DW-D26451K      $51.58
DW-D26453K      $66.25
DW-D26441KR      $60.67
DW-D26451                           $53.00
DW-D26441                           $36.00
DW-D26453                           $72.36
DW-D26450                           $95.00
Mark WillsTopic AdvisorCommented:
Yep quite correct, was not putting the balancing entry in correctly...

Swap the InventoryValuationSummary code for :

SELECT I.LocalSKU, I.ItemName, I.QOH, I.Price,sum(IIf(i.qoh=0,0,IIf(c.planned+c.ordered>i.qoh,i.qoh - c.planned,c.ordered))*c.current_cost) AS stock_valuation, IIf(i.qoh>0,stock_valuation/i.qoh,avg(c.current_cost)) AS unit_cost
FROM Inventory AS I LEFT JOIN AvailableStockCost AS C ON ((I.Qoh>C.Planned) Or (I.Qoh=0 And C.Planned=0)) AND (I.LocalSKU=C.LocalSKU)
group by I.LocalSKU, I.ItemName, I.QOH, I.Price

And I think you will find that we were both wrong in one or two calcs.... e.g. doing it manually DW-D26451K should be over $62 dollars - the last 'n' PO's are all $62 or higher...
Here are the new results using the above (moved description to far right).
LocalSKU		QOH	Price	valuation	unit_cost	ItemName
DW-397531-00	0	$15.02	0	$12.13	DeWalt DW6187 Guide Bushing Base Plate
DW-D21002		0	$60.79	0	$55.26	Dewalt D21002 3/8"""" VSR Drill 120v Keyed chuck"
DW-D26441		5	$46.53	180	$36.00	Dewalt D26441 1/4 SHEET SANDER
DW-D26441K	0	$49.00	0	$43.70	Dewalt D26441K 1/4 SHEET SANDER KIT
DW-D26441KR	6		156	$26.00	DeWalt D26441KR 1/4 Sheet Palm Grip Sander Kit with 
DW-D26450		3	$66.63	171	$57.00	Dewalt D26450 5"""" ROS with PSA Pad and Dust Col
DW-D26451		5	$69.01	265	$53.00	Dewalt D26451 5"""" ROS with Hook & Loop Pad and Dus
DW-D26451K	17	$69.00	1062.88	$62.52	Dewalt D26451K 5"""" ROS Kit with Hook & Loop Pad an
DW-D26451KR	0		0	$35.00	DeWalt D26451KR 5"" ROS Kit with Hook & Loop Pad and 
DW-D26453		5	$83.49	361.8	$72.36	Dewalt D26453 5"""" VS ROS with Hook & Loop Pad and D
DW-D26453K	12	$89.00	795	$66.25	Dewalt D26453K 5"""" VS ROS Kit with Hook & Loop Pad 
DW-D26670		0	$83.60	0	$76.00	Dewalt D26670 Heavy Duty Laminate Trimmer

Open in new window

UprightdudeAuthor Commented:
Actually because the last order for D26451K  was at 0.00 it needs to calculate that cost into the total therby bringing down the actual real cost for the item.  As an example this would be where a company has given us said number of products free for ordering other products.  So in order for us to account for a real price for the products in inventory we want that free product to be accumulated into the overall price.

As explained earlier
DW-D26451K should pull records
all of the 1 from Record number 25 at 0 costs  =0.00
all of the 12 from record number24 at 62.74 costs =752.88
only 4 of the 5 from record number 11 at 62.00=248.00
Summed up to equal 1000.88 divided by quantity on hand 17 for a realcosts of 58.87
As said in my first post, i was leaning out the window a bit, when thinking sql wasnt enough. Nice to see that i was wrong:
What occured to me looking at it again is - what about a situation when you have more in Inventory that Ordered.
Like for instance 200 DW-D26451K in Inventory. Is the unit_cost expected to drop to 7,48
I noticed that i hadn't accounted for that situation in my exampel either:
UprightdudeAuthor Commented:
Thank you very much this was exactly what I was looking for.  I appreciate everyones input here on Experts exchange.  Thank you all for the input and solutions.
Mark WillsTopic AdvisorCommented:
Very happy to have been of help, and a little disappointed that I had not properly checked all your requirements in fine enough detail to have caught those "features" earlier.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.