"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:ci**********@coranto.ucs.mun.ca...
I'm assuming David Mitchell is referring to part of Albert's web site?
Could I ask if Albert or David would provide a URL? Or, if it's in
reference to an earlier post on cdma, could one of you tell me the subject
title and I'll check my news server or google it?
Actually, I am not sure what Quantity on hand, and I actually not sure even
what post!!!
I can't even find the reference on a newsgroup google search. However, I do
remember this issue. Just can't remember if I sent the person a piece of
code....or talked about "how" to do this!
At any rate, if my memory serves me, I simply suggested, or posted some
simple code that took the productID, and calculated the Quantity on hand.
This is done simply by adding up all inventory additions and then
subtracting any product in the "invoice details" to get the actual quantity
on hand. This is great approach to inventory, as then you don't have to
write ONE LINE of code to update the inventory stuff...you just calculate it
on the fly. This means you invoice form can add, or delete invoice details,
and no special "update" code is needed to be run to try and maintain the
actually totals of the inventory. (so, you just delete the invoice, or if
you want you can edit, or delete part of a order, and the quantity on hand
magically updates with NO code!!). It also means the totals are always
correct, since no "code" runs to try and update the quantity on hand!
The function I suggested to use is something like:
Public Function MyQuanityOnHand(ProductID as vID) as long
' code goes here to build two sql statements (shoved into reocrdsets) to
calculate
' results
' build query to sum all orders with ProductID
' get result
'build query to sum all inventory additions for ProductID
'get result
Then, those results are something like:
MyQuanityOnHand = ( Sum of Inventory of product - Sum of Product Sold)
end function
Of course, we all know that opening, and closing a reocrdset EACH time is
costly. The original poster was using this "function" in a report to display
the inventory for a list of products. You see the performance numbers here:
The real point here is that calling a function in a query, or in a report
for "each" record is very expensive. As you can see the numbers:
Original post with a 100 records, and a function: 20 seconds.
My example with 2500 products, and a 125,000 detail records: less then 1
second!!
In other words, even a file with 125,000 order details from invoices is
absolute NOTHING for ms-access to process in "real time", and thus we NEVER
actually store the "quantity on hand" in the database..but in fact,
calculate it on the fly.
I did post an example zipped file with the 125,000 "order details" records,
and 2500 inventory records.
As mentioned, to calculate all of the results and "join" those 125,000
records to 2500 products takes less then 1 second on most computers.
Fact is, if you limited the users search to a particular product then were
to display the quantity on hand, you can see that is approach scales quite
well. Since we would be working with just one record, not the 2500 possible
product records, then I see no reason why a product with even 1000 orders
against it would not be instant.
You can find the data, and the sample query here (right at the bottom):
http://www.attcanada.net/~kallal.msn.../msaccess.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com http://www.attcanada.net/~kallal.msn 
