update for running sum

[miss031]

I'm not sure if this has been covered here. I have a form that adds
individual purchases to a customer's account, updating a table that records
the item urchased, the price and the quantity. I am trying to set up
invocies and payments. I have a tranactions table which appends a record for
the amount of the invoice, and also appends a record for the amount of the
payment. THe problem I have is updating the amount of the invoice for each
purchase added to it. I know that you aren't suposed to store calulated
values, but I can't find a normalized route that does not consume to many
resources by running select queries with numerous tables.

If this is my path, I would like to write and update query that would update
the invoice amount in the invoices table with the total purchase amount
(includng surcharges and taxes, all derived from an invoice query) each a
purchase is added to the invoice.How do I find the sum of the purchases for
that invoice, and then add the new amount to that total, and store it in the
tranactions table?

 

[Evi]

This is a standard database design. It is very unlikely that you should be
storing the total amount for the invoice in your database if you have
already stored the amount for the individual purchases. I don't know your
design but I can't imagine why it is necessary. The only calculation result
you may need to store are calculations which can change - graduated discount
rates
The data which you do need to store, is the amount that each item cost at
the time of purchase and any additions or deductions at that time. These can
either be per item or per order,

The *basic* design of this sort of thing is
TblItem
ItemID (PrimaryKey)
Item
RRP (recommended retail price - this may not be the price at which it is
sold)

TblCustomer
CustomerID (PK)
any details about your customer eg address but nothing to do with their
current order

TblOrder
OrderID(PK)
OrderDate
CustomerID (foreign Key field)
OrderCost fields ie NOT the total cost of the items. (eg Discount, shipping
costs - anything that applies to the whole order rather that individual
items within it
If, for example, you have a discount that is calculated on a 'the more you
buy, the cheaper it gets' basis, then you need to store the rate for that
within your database. If it changes per item, then it will need to be stored
either in the items table if it is a one-off amount (if you have more than
25 of this item, then shipping goes free) or in a DiscountRate table.
Because your discount rate may change at any time, you will need to store
the current amount in your Orders table. The VBA code in your form will
lookup the current rate at that time
If discounts apply to both then you will need both a ItemDiscountRate and
and OrderDiscountRate table.



TblOrderItems
OrdItemID (PK)
OrderID (Foreign Key field)
ItemID(Foreign key field)
CurrPrice (current price of the item)
Quant (quantity of the item)
any details that apply to that particular item only as it is in that
particular order.
VAT (this is the current VAT percentage - not the VAT total because that can
be done by calculation - it can be a Default Value in your table)

You will use a form to fill in the details and use VBA to look up the RRP
and put it into CurrPrice. It can then be adjusted if you wish it to be

Evi