Access SQL Question

Posted on 2009-05-19
Last Modified: 2013-11-28
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
Question by:Uprightdude
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
  • +2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24425991
You need a Date Field in the Purchase Order Table.

Author Comment

ID: 24426012
Would it matter that the purchase order tables are sequential thus a Purchase order number being the highest would be the latest?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24426411
I guess...

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

How far have you gotten on this yourself?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 24426853
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.  
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24428334
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.

Author Comment

ID: 24432938
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

Author Comment

ID: 24434209
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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24438528

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.


Author Comment

ID: 24451496
Okay I Requested attention
Hopefully someone can assist me.
LVL 27

Expert Comment

ID: 24457198
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 :-)

Expert Comment

ID: 24462912
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.?

Author Comment

ID: 24464059
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?  
LVL 51

Expert Comment

by:Mark Wills
ID: 24464206
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 ?

Expert Comment

ID: 24465538
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.
LVL 51

Expert Comment

by:Mark Wills
ID: 24466594
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.


Expert Comment

ID: 24468568
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.


Author Comment

ID: 24477697
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.  
LVL 51

Expert Comment

by:Mark Wills
ID: 24479366
>> 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...

Author Comment

ID: 24485848
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
LVL 51

Expert Comment

by:Mark Wills
ID: 24486266
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


Author Comment

ID: 24486651
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
LVL 51

Accepted Solution

Mark Wills earned 500 total points
ID: 24486740
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.


Expert Comment

ID: 24487413
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:

Author Closing Comment

ID: 31583192
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.
LVL 51

Expert Comment

by:Mark Wills
ID: 24491623
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.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question