Customer Accouts Statements for Invoicing database

[Freddie81]

Hello all,

I've finally reached the end of my database design and need to create a report to print customer statements. I have created a union query from the invoices table, credit notes table and payments table to show the current transactions for the customer. The problem starts afte this.....

1. How do I actually calculate and use the opening balance on the statement so that the query can include it in the running balance?
2. How on earth do I calculate the 30 days, 60 days and 90+ days sections?



I' ve tried searching youtube for videos but alas....nothing lol

[CJ_London]

1. if you are creating a report, you can set a control to 'running sum' so include your opening balance in your union query

2. investigate the partition function used in sql

[Freddie81]

So...

Create atextbox on the report itself that runs the running sum?
How would I do the opening balance inside the union query?

[CJ_London]

How would I do the opening balance inside the union query?

however you calculate the opening balance at the moment and add another union

I use a single transaction table, not 3 as you do, so it is much easier to do calculations. Assuming you have a field called tranDate and want to run a statement from sDate to eDate my union query might look something like

SELECT sDate as tranDate, "OpeningBal" as tranDesc, Sum(tranValue) as Amount
FROM tblTrans
WHERE tranDate<sDate
GROUP BY sDate, "OpeninBal"
UNION SELECT tranDate, tranDesc, tranValue
From tblTrans
WHERE tranDate BETWEEN sDate and eDate

[Freddie81]

Maybe it would help if I gave you the code I've got in my query.....

SELECT InvoiceT.InvoiceID AS [Doc Nr], " Invoice" AS Document, InvoiceT.InvoiceDate AS TransDate, InvoiceT.InvoiceTotal AS Amount, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT INNER JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) INNER JOIN InvoiceT ON CustomersT.CustomerID = InvoiceT.Customer
WHERE (((InvoiceT.InvoiceDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]) AND ((InvoiceT.IsPaid)=False) AND ((CustomersT.ActiveCustomer)=True))
ORDER BY InvoiceT.InvoiceDate
UNION ALL SELECT CreditNoteT.CreditNoteID AS [Doc Nr], " Credit Note" AS Document, CreditNoteT.CreditDate AS TransDate, [CreditNoteT].[TotalCredit]*-1 AS Amount, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT INNER JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) INNER JOIN CreditNoteT ON CustomersT.CustomerID = CreditNoteT.Customer
WHERE (((CreditNoteT.CreditDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]) AND ((CustomersT.ActiveCustomer)=True))
UNION ALL SELECT PaymentsT.InvoiceID AS [Doc Nr], " Payment" AS Document, PaymentsT.PaymentDate AS TransDate, [Amount]*-1 AS Expr1, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT INNER JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) INNER JOIN PaymentsT ON CustomersT.CustomerID = PaymentsT.Customer
WHERE (((PaymentsT.PaymentDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]) AND ((CustomersT.ActiveCustomer)=True));


This basically shows me all the records from all 3 tabls that is is between the tart date and the end date enterd in a form.
I need to have the qury show all that info for just a specific customer too ad then the opening balance problem

[CJ_London]

As I said, I use a single transactions table as it makes it easier - you've just proved my point

I don't understand why you are bringing through details such as telephone and logopath as part of the transactions. Like an invoice you should have header data in the main form/report and then transactions would appear in a subform/report.

In your case, to get the opening balance you will need the same query as shown but with different date criteria per the first SELECT my example, then group it, then add to your existing union query

My example is just a freetyped example - just add customerID to both parts of the union query (and group on it in the first SELECT) - probably need to add other data as well such as invoice number, payment reference etc

[Freddie81]

Okay I will take a lok at your example nd try to get it to work on my situation

I've got the logo path etc i there because the database is for distribution so each client will be able to change the logo and details of the company as it suits them. There is no fixed header theme if that makes sense

[CJ_London]

There is no fixed header theme if that makes sense

not to me, but so long as it does for you

[Freddie81]

Okay s I have a little progress LOL

New bug:

I'm trying to calculate the 30 days, 60 days and 90 days sections. Im using unbound text boxes o the report.
Screenshot of Query for this purpose:


On the text box for 60 days Im tryig this expression:



When I run the report it shows # Error

I think maybe the TransDate section might be wrong?

[CJ_London]

think you need something like

TransDate<Statementdate-60