Look For Balances

[joesmithf1]

Hi Everyone,

I am fairly new to the whole Database thing, so hopefully you can help. So i created three tables; MONEYOWE.tbl, INVOICE.tbl, and PAYMENT.tbl. Basically, moneyowe.tbl is how much a client owes me. Invoice.tbl is the invoice i'll send the clients, letting them know that they still owe me money. Payment.tbl is when they send me the payment.

NOTE: each client could have multiples payments for each invoice(Invoice Number).

TABLE LAYOUTS(btw, is this the correct table setup?):
Money.tbl - clientID, oweamount
Invoice.tbl – invoiceID, clientID, InvoiceAmount
Payment.tbl – invoiceID, PaymentID, ClientID, PaymentAmount

What i want to do is run a query every month to compare the amounts from all three tables and see what balance is left.

So how do i go about doing this? Do i first do a SELECT query(call it Payment Query) from PAYMENT, and then do a “GROUP BY” on InvoiceID, to get ONE record per Invoice? And then do another select query to go against the “Payment Query” and MONEYOWE.tbl and INVOICE.tbl to get the balance?

OR can I do one SELECT statement against all tables to get the balance?

I just don’t know how to approach this or what is the “best practices” to do this.

Please advise.

Thank you.

 

[plog]

First, Money.tbl is unnecessary because you can determine this from the other data you capture. Second, ClientID shouldn't be a field in Payment.tbl because you already have an InvoiceID which you can use to get the client.

Now to get the balances you would do it as you suggested. Create a sub-query based on Payment.tbl GROUPING BY invoiceIDand summing the PaymentAmount field. Next create another query based on that query and the Invoice.tbl table. The key here is making a LEFT JOIN from the Invoice.tbl to the subquery linking them on InvoiceID. You want to show all data from Invoice.tbl and only those records that match in the subquery.

 

[joesmithf1]

great! thanks for the tips!

joe