Looping Calculation 1

[MasterRacker]

I have a data set like the following:

[tt]TransactionID TransactionDate ProductID UnitsReceived UnitPrice
200 08/30/09 123 10 10.00
175 07/15/09 123 5 7.50
140 07/05/09 123 11 9.00
[/tt]
If I have 12 units on hand, the stock value calculation would be 10@10.00 + 2@7.50. If I had 17 units on hand, the value would be 10@10.00 + 5@7.50 + 2@9.00. It's alwas the most recent shipments whose prices are used since the older prices are used elsewhere in disbursement calculations.

I'm think I'm going to load a recordset sorted in descending date order and just use MoveNexts to iterate through it to do the calculations.

I did have another idea however to use dlookup and the MAX date and then if I needed more units, to do another dlookup based on MAX date less than the date I just used.

Just out of curiosity, would 2 or 3 dlookups execute faster or slower than loading a full recordset that may have a years worth of receipts? (Which may not be that many - this is more of a theoretical question.)

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[formerTexan]

Why not use the TOP predicate in a Select query?

SELECT TOP 3 tbl.TransactionDate, tbl.UnitPrice FROM tbl ORDER BY tbl.TransactionDate DESC;

Cheers, Bill

 

[MasterRacker]

I wasn't aware that TOP worked in Access SQL. However, the main problem is while I think the vast majority of the time I will only need 1,2, or maybe 3 to cover the quantity on hand, there's no rule that says that.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

I generally create a table of numbers [tblNums] in my applications with a single field [Num] and values from 1 to some big number. Assuming you have this table you can create a query of price range quantities:
[qselTransRange]

SELECT tblTransactions.TransactionID, tblTransactions.TransactionDate, tblTransactions.ProductID, tblTransactions.UnitPrice, tblTransactions.UnitsReceived, Val(Nz((SELECT Sum(UnitsReceived) From tblTransactions t WHERE t.ProductID = tblTransactions.ProductID AND t.TransactionDate > tblTransactions.Transactiondate),0)) AS FromUnits
FROM tblTransactions
ORDER BY tblTransactions.TransactionDate DESC;

Then to get the cost of a specific number of products:

SELECT qselTransRange.ProductID, Sum(qselTransRange.UnitPrice) AS SumOfUnitPrice
FROM tblNums, qselTransRange
WHERE (((tblNums.Num)>[fromUnits] And (tblNums.Num)<=[FromUnits]+[UnitsReceived]) AND ((tblNums.Num)<=[Enter Number Sold]))
GROUP BY qselTransRange.ProductID;

I don't care for parameter prompt queries so create a control on a form to get the Number Sold.

Duane
Hook'D on Access
MS Access MVP

 

[idbr]

Hi Jeff,

I think multiple lookups would get ugly *really* quickly.

I'd say a recordset would be a better option for readability - and don't forget that you only read records into your set as you access them, so iterating through would (imo) also perform better.

Regards, Iain

 

[MasterRacker]

Duane,
I think you're reading more into my problem than is there. I know the quantity on hand for each product. I simply need to use that, per receipt, per product to see what we paid for what we have on hand, for each product.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

Just replace the [Enter Number Sold] with the quantity on hand for each ProductID. This will result in the current inventory value. No code, no recordsets, no looping or iteration, no dlookup, no fuss. Just SQL which should perform better than any code.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

I'll try and implement that and see what it does. I think I've got a vague idea, but just looking at the code, I'm not seeing how it deals with the fact that unit price for any given product is not constant.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

The first query returns values like
[tt][blue]
Tr..ID TransDate ProdID UnitPrice UnitsRecvd FromUnits
200 8/30/2009 123 $10.00 10 0
175 7/15/2009 123 $7.50 5 10
140 7/5/2009 123 $9.00 11 15
[/blue][/tt]
This can be used to suggest the unit quantities from the [FromUnits] column to the [FromUnits]+[UnitsReceived] will be priced at the [UnitPrice].

The second query takes advantage of this "units range" to identify the cost of every single unit (using tblNums). For instance if the on hand quantity is 10 or less, each unit will have an individual value of $10. If the quantity on hand is 12, the first 10 will be priced at $10 and the next 2 at $7.50.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

OK, Finally had a chance to revisit this. (I've changed the names to protect the innocent.) I'm getting results for 100 of 137 products (may be data issues) and it appears to work. What concerns me is it takes over a minute to run using a copy of the DB that's on my local hard disk (no network issues.) To get value for all products I joined your query with another that gives me the stock on hand. I'm wondering if there's a different way to do this. My final version of your 2nd query

SELECT StockValueRanges.ProductID, StockOnHand.ProductName, StockOnHand.OnHand, Sum(StockValueRanges.UnitPrice) AS SumOfUnitPrice
FROM Numbers, StockValueRanges INNER JOIN StockOnHand ON StockValueRanges.ProductID = StockOnHand.ProductID
WHERE (((Numbers.Num)>[FromUnits] And (Numbers.Num)<=[FromUnits]+[UnitsReceived] And (Numbers.Num)<=([StockOnHand].[OnHand])))
GROUP BY StockValueRanges.ProductID, StockOnHand.ProductName, StockOnHand.OnHand;

I have a related issue as well. For the value of stock on hand we're doing here, I need to calculate backward from the most recent batches received. I also have an actual disbursement transaction. That one is one product at a time rather than everything, however the value needs to calculate from the last received batch that still has items in stock, forward through received batches until the qty disbursed is reached. I'm not sure if this method can be adapted to that or not.

(I'm also not sure if our auditors will accept this if they don't understand the calculation, but that's a separate issue.)

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

Your original posting had one product. I can't picture how your tables are actually structured, what fields are indexed, or what you mean by "calculate from the last received batch that still has items in stock". If you can't do this than I doubt your auditors will accept it.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

Ah, in my initial example I was trying to show what I need to do for each product. This is a simple inventory database. Products are received in batches at a specific unit price per product but the price for any given product can vary for different receipts. End result is a store room with X of each product on hand. Products are disbursed to various depts as needed. Cost to depts. is calculated on a FIFO basis. I don't store any calculated values so stock on hand is simple sum of receipt - sum of disbursements.

For now, let's ignore the disbursement calculation. As I'm typing this another issue occured to me that complicates it further. I'll revisit it later.

The relevant core table structure is very simple:[small][tt]
InventoryTransactions Products
--------------------- -----------
TransactionID /-ProductID
TransactionDate / ProductName
ProductID-----------/
UnitPrice Departments
UnitsReceived ------------
UnitsDisbursed /-DepartmentID
DeparmentID----------/ DepartmentName[/tt][/small]

I tried to keep it simple and not use a transaction type. If it's a receipt, UnitsReceived is > 0 and UnitPrice is what we were charged. If it's a disbursement, UnitsDisbursed is > 0 and UnitPrice is manually calculated at this time.

The sample data set shown in my OP comes from [small][tt]
PARAMETERS ProdID Long;
SELECT InventoryTransactions.TransactionID, InventoryTransactions.TransactionDate, InventoryTransactions.ProductID, InventoryTransactions.UnitsReceived, InventoryTransactions.UnitPrice
FROM InventoryTransactions
WHERE (((InventoryTransactions.ProductID)=[ProdID]) AND ((InventoryTransactions.UnitsReceived)>0))
ORDER BY InventoryTransactions.TransactionDate DESC;[/tt][/small]

The report I'm doing is 1 line per product to show qty. on hand and the value of what's on hand per product based on the prices paid most recently for each product.



Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

If your performance is slow, I would consider creating a query to update/create a QtyOnHand table. Use this against the pricing query. If you have multiple products from multiple orders to price in one query, this might not work.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

The StockOnHand I referenced earlier is a "view" that should only be running once. It outputs every product and the qty on hand. I don't know that writing that to a table would do much for the value report, the on hand query runs almost instantaneously.

I'm not working on the basis of orders, just products. This particular report simply lists all items in stock and their value. It would be trivial, but for the variable unit price.

Final Result:
[tt]
Product OnHand Value
ProductA 5 $32.22
ProductB 13 $23.98
ProductC 17 $123.45
ProductD 5 $347.88
...[/tt]

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

Looking back at a previous post

I need to calculate backward from the most recent batches received. I also have an actual disbursement transaction. That one is one product at a time rather than everything, however the value needs to calculate from the last received batch that still has items in stock, forward through received batches until the qty disbursed is reached. I'm not sure if this method can be adapted to that or not.

My suggestion does price out value based on cost of most recent.
Consider these transactions
[tt]
TransactionDate ProductID UnitsReceived UnitPrice
01-Sep-09 111 17 $13.50
15-Aug-09 111 8 $11.50
01-Aug-09 111 15 $11.00
01-Jul-09 111 7 $12.00
30-Aug-09 123 10 $10.00
15-Jul-09 123 5 $7.50
05-Jul-09 123 11 $9.00
[/tt]
These On Hand
[tt]
ProductID OnHand
123 20
111 31
[/tt]
My queries result in
[tt]
ProductID OnHand SumOfUnitPrice
111 31 $387.50
123 20 $182.50
[/tt]
This was my final query:

SELECT qselTransRange.ProductID, tblProductOnHand.OnHand, Sum(qselTransRange.UnitPrice) AS SumOfUnitPrice
FROM tblNums, qselTransRange INNER JOIN tblProductOnHand ON qselTransRange.ProductID = tblProductOnHand.ProductID
WHERE (((tblNums.Num)>[fromUnits] And (tblNums.Num)<=[FromUnits]+[UnitsReceived] And (tblNums.Num)<=[OnHand]))
GROUP BY qselTransRange.ProductID, tblProductOnHand.OnHand;

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

If you substitute a qryProductOnHand for your tblProductOnHand and add in the ProductName, the query I had in my post from 30 Sep 09 17:15 is identical and does what I want. The only issue is performance. This is only going to be run occasionally though, so that may not really be an issue.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

I would make sure that Num, ProductID, and TransactionDate are indexed in all tables. Then thank the SQL gods since you didn't have to write any code

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

Everything mentioned is already indexed. Looks like performance is what it is. Now I get to start on disbursments.

(There's always something that kills the "oh yeah, that'll be really easy" statement. [LOL])

Thanks.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]