Update query for many deleted records

[xolo]

I have a "Purchase" form and a subform in it. When I delete record(s), I want the stock quantity of the related product(s) in the "Product" table to be deducted the same quantity as what was stored on the "Quantity" field in the subform. With this way, the stock in the "Product" table is always updated whenever I delete the record(s).

I managed to do this by using an update query with a criteria on the "Product Id" pointed to the "Product Id" in the subform which is trigerred at the OnDelete event.

The problem is when I select more than one record at once to delete, the update query can only update one record. Does any body know how solve this?

 

[pbaldy]

I wouldn't try to store the quantity on hand at all. This link has a discussion of the topic in general, as well as some thoughts on your specific problem near the end:

http://allenbrowne.com/AppInventory.html

 

[xolo]

Thanks for the answer. I thought it would be too slow if everytime the program had to perform the calculation from the beginning because I have so many transactions every day.

 

[pbaldy]

I guess that depends on how many that is, but Access can handle quite a lot. Welcome to the site by the way.

 

[xolo]

I have changed my program to call the quantity-on-hand only when it needs it. Now, my next question is, to calculate the quantity-on-hand, which one is better:
1. Using function, or
2. Calculating the quantity-on-hand and store it in a temporary file

I have tried both methods and it seems the 2nd way works faster.

 

[namliam]

storing calculated values is BAD

 

[xolo]

storing calculated values is BAD

Can you be more specific why it is bad?

 

[namliam]

It is rule #1 in database design!
The problem of storing values is that anytime anything in the details is changed you need to change the total as well.
In a proper database you do NOT store calculated values!

 

[xolo]

It is rule #1 in database design!
The problem of storing values is that anytime anything in the details is changed you need to change the total as well.
In a proper database you do NOT store calculated values!

It makes sense for a multi-user environment but for a single-user program (like my case), no details are changed after the calculation is called and before the report is closed.

 

[namliam]

Yes well... for a report perhaps, but still in 99% of the cases... no no no...

Unless the win is huge, dont store calculated values.... it is a golden rule!

 

[xolo]

Yes well... for a report perhaps, but still in 99% of the cases... no no no...

Unless the win is huge, dont store calculated values.... it is a golden rule!

Thanks. Now it all makes sense to me. I will keep this rule in mind when designing my database. Thanks again!