updating database calculated field through VB code

iwyll · February 20th, 2009, 11:21 AM

i'm new to VB and Access and this is my situation:
i have a database with different fields such as CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_TOTAL $
everyday i get new information about some customers with these same fields but the CUSTOMER_TOTAL $ field might have a different number. i'm going to have this new information in a new table in the database.
now what i want to do is somehow use some coding that allows me to:
look in the new data if any of the customers in the main table has a new CUSTOMER_TOTAL $ value in the new data, and add the new value to the value in the master table. we will be doing this same operation daily so i will also need some way of storing the code so i don't have to re-type it every time

i was thinking that this could be done somehow through VB coding, but i don't know how to do it. whatever info will be greatly appreciated

mmcdonal · February 23rd, 2009, 12:42 PM

So you will have two tables that look like this:

tblCustomer
CustomerID
CustomerName
CustomerTotal (is this a running total?

tblTransaction
TransactionID
CustomerID - look up to tblCustomer
TransactionTotal

and that last line needs to be added into the CustomerTotal in tblCustomer?

First question: Why would you do this when this design without CustomerTotal in tblCustomer will yield the Customer Total with a simple query? No need to transfer all these totals to tblCustomer since they are already joined such that a simple query / report will yeild the current customer total in real time.

Storing this kind of totaled number in a table is not generally called for in database design. Is there a reason you need to store this value when it is stored already?

Code:

SELECT tblCustomer.CustomerName, Sum(tblTransaction.TransactionTotal) AS SumOfTransactionTotal
FROM tblCustomer INNER JOIN tblTransaction ON tblCustomer.CustomerID = tblTransaction.CustomerID
GROUP BY tblCustomer.CustomerName;