Statement of Account

[Seph]

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.)

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.

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

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!

 

[plog]

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.

 

[CJ_London]

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

 

[Seph]

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

 

[GaP42]

I agree and have decided to implement it as such.

The trick now is to get them in a query format as attached above

But you have not implemented what CJ said:

you really need another table between invoices and payments to show the split

 

[Seph]

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.

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.