Help needed on Accounting Problem!

[Bob Vance]

My database for Invoice/Statements is based on say 2 Tables Invoices (Money
Owed) and Table (Amount Paid) each have ClientID Numbers
But this format means adding up all totals for each ClientID, What I am
looking for is a system that totals each of the last 3 Months for each
ClientID, so at the end of each month it creates a total the last total 3
Months would include all Expenses not paid 3 Months back. Is there some
simple written procedure I can follow to do this! or maybe a sample
database!

 

[kc-mass]

Hi Bob

Use a query, something like :

SELECT tblPayments.ClientID,
Sum(tblPurchases.PurchaseAmount) AS SumOfPurchaseAmount,
Sum(tblPayments.PayAmount) AS SumOfPayAmount,
Sum([PurchaseAmount]-[PayAmount]) AS Net
FROM tblPurchases INNER JOIN tblPayments ON tblPurchases.clientID =
tblPayments.ClientID
GROUP BY tblPayments.ClientID;

Then add a WHERE clause before the GROUP clause to limit it to a date range.

Regards

Kevin

"Bob Vance" <[email protected]> w

rote in message news:[email protected]...

 

[De Jager]

My database for Invoice/Statements is based on say 2 Tables Invoices
(Money Owed) and Table (Amount Paid) each have ClientID Numbers
But this format means adding up all totals for each ClientID, What I am
looking for is a system that totals each of the last 3 Months for each
ClientID, so at the end of each month it creates a total the last total 3
Months would include all Expenses not paid 3 Months back. Is there some
simple written procedure I can follow to do this! or maybe a sample
database!