Milo Khoo Milo Khoo - 8 months ago 42 Question

Calculate result from multi table in database

I have a question in my VB.NET POS Development and could't find a solution myself. I'm currently using

MS Access
as my database.

I have two database table as shown below:

SalesReceipt(#Receipt_ID, Sales_Date, Receipt_Number, Customer_ID, Sales_BDiscount, Sales_ADiscount, Sales_Payment)

Customer(#Customer_ID, Customer_Name, Customer_Contact, Customer_Debt)

NOTE : BDiscount = Before Discount / ADiscount = After Discount

In my system, one customer can have many SalesReceipt. Now my problem is how can I update the correct
for customer? My logic is update the respective customer's
by looping every row in
and calculate the debt by doing something like :

totalDebt = totalDebt + (Sales_Payment - Sales_ADiscount)

But I not sure how can I make sure it only loop and calculate the result for the selected customer only. How can I achieve this correctly in .NET?

Answer Source

If you want to calculate totalDebt per customer you can use query

SELECT Customer_ID, sum(Sales_Payment - Sales_ADiscount) as totalDebt FROM SalesReceipt
 GROUP BY Customer_ID

Result contains totalDebts aggregated by Customer_ID and can be used to update Customer (all the looping and calculating is done by a database engine).

The query can also update be more complex to do even update for you.