Statement of Account (1 Viewer)

Seph

Member
Joined
Jul 12, 2022
Messages
72
Hello there everyone,

Looking for a nudge in the right direction please.

I already have a StatementR report that reflects Invoices details (InvoiceNumber, InvoiceAmount, AmountDue etc.)

1686735446454.png


The AmountPaid field on the report is calculated in the StatementRQ query:

Amount Paid: IIf([Paid],[InvoiceAmount],Val(0))

Simple, but its been working very well.

I'm looking at expanding the functionality by creating another Report called "Statement of Accounts", that will have a more detailed break down of transactions coming in and out for a desired client.

E.g.
1686735695648.png


I've created another table PaymentsT to keep record of payments made to clients.

1686735794874.png


I'm certain a query is the best way of going about this, probly a union query. But the field names in each table is different.

Any advice would be appreciated.

Thank you!
 
Payments aren't directly related to Invoices? That seems wrong.

A UNION query is usually a hack around poorly structured data. The report you want should be straight forward to build and not require it---if you collect payment information at the invoice level.
 
Actually payments and invoices have a relationship but not one to one. A payment might cover several invoices and maybe allow for credit notes as well. Or it might be a partial payment for some reason. Or it might be unassigned due to a payment error such as an overpayment.

you really need another table between invoices and payments to show the split
 
Actually payments and invoices have a relationship but not one to one. A payment might cover several invoices and maybe allow for credit notes as well. Or it might be a partial payment for some reason. Or it might be unassigned due to a payment error such as an overpayment.

you really need another table between invoices and payments to show the split
I agree and have decided to implement it as such.

The trick now is to get them in a query format as attached above:unsure:
 
To give an update for anyone whom in future would like to implement this.

I ended up creating a Temp table and with the help of VBA, populated it with data from both the InvoiceT and PaymentsT tables.


Code:
Private Sub Command0_Click()

SQLstatement = "DELETE FROM TmpStatementOfAccountsT"
DoCmd.RunSQL (SQLstatement)

SQLstatement = "INSERT INTO TmpStatementOfAccountsT (TransactionID, TransactionDate, CustomerName, Amount) SELECT InvoiceID, InvoiceDate, CustomerName, InvoiceAmount FROM InvoiceT WHERE CustomerName = 1 AND InvoiceDate >= #2023/01/01# AND InvoiceDate < #2023/12/31#"
DoCmd.RunSQL (SQLstatement)

SQLstatement = "INSERT INTO TmpStatementOfAccountsT (TransactionID, TransactionDate, CustomerName, Payments) SELECT PaymentsID, PaymentDate, CustomerName, PaymentAmount FROM PaymentsT WHERE CustomerName = 1 AND PaymentDate >= #2023/01/01# AND PaymentDate < #2023/12/31#"
DoCmd.RunSQL (SQLstatement)

End Sub

I then created a Query based off of that table and then ordered first by TransactionDate and then by TransactionID.

Hope this helps someone else.
 

Users who are viewing this thread

Back
Top Bottom