Stock Update using VBA in Access 2007

[Rainlover]

Rainlover, quote from that very link you provided:



"One of the basic rules of data normalisation is not to store dependent values. Break the rule at your own peril: it takes more work than doing the job properly; it diminishes the queryability of your data; it leaves you without any certainty that your answer is correct."

Any calculated value is a dependent value, especially 'running balance' or summary calculations.

June

Thank you for replying.

I did get the feeling that you had experienced something personally that supports your strong point of view. I would have been interested if that were the case.

Personally I try to keep an open mind when it comes to Rules. Mostly I follow them as they were intended, while at the same time keeping an open mind to possible variations. So far I have not experienced this endless headache.

Even Allen Browne in the link provided, shows an example where there are times to stick to the rules and then there are other times that they need a little bending.

[thanosgr]

Gee Thanks

i think its working 80%...
still has usability problems...

[June7]

Yes, Rain, I have a project that 'breaks' rules but one I haven't broken is saving aggregate calcs. Maybe only because I haven't built a db anything like an accounting or sales or inventory application where a running balance is needed. I just want to stress to novices the importance of carefully considering the implications of saving calculated values, especially aggregates. Often they think they must when they really don't and shouldn't.

[thanosgr]

Another question is how to use group by in update.
since there is the form, and do multiply insertions to update stock(stock-quantity) but for multiply productid dont work.. i try to use group by productid but it gives sql error
here is the code, without the group by works but for the same productid

mysql = "UPDATE product INNER JOIN TransactionLine ON " & _
"[product].ProductId = TransactionLine.ProductId " & _
"SET product.apothema = product.apothema" & _
" - forms![TransactionLine].sumqtr" & _
" where product.Productid=forms![transactionline].Productid" & _
" group by product.Productid"

[Rainlover]

A query with Group By cannot be updated.