Statement Of Accounts

[finbarrodonovan]

does anyone have a sample of a report that will show the statement of account of a client. it would show the opening balance, the list of invoices and payments and the closing balance.
I need to create one and dont know where to start
Regards
Finbarr

[DaveKeatley]

don't have an example, but what data do you have to work with? What report engine are you using to create your report?

it really all depends on what the table structure is.

I would seperate out the data to list the inital balance on the report header somewhere, then create two subreports that would go in the detail of a report sorted by date. Color Code the text for invoices as red and payments as black (or green ) and offset the two items

Would look something like this

Header-------------------------------------------
mr guy's account inital balance $1.00

detail (sort by date)--------------------------
Date
-----(sub report one Payments)-------------
Payment Amount
-----(sub report two invoices)----------------
invoice number total

-----(report footer)-----------------------------
(formula to calc total)

[finbarrodonovan]

what i would like to have is

date detail debit credit Balance
1/1/03 openingbal 1231
2/1/03 Invoice 31

[DaveKeatley]

ok so

what data do you have to work with? What report engine are you using to create your report?

[finbarrodonovan]

what i would like to have is

date detail debit credit Balance
1/1/03 openingbal 1231
2/1/03 Invoice 31 1262
10/1/03 Payment 55 1212
etc.....

at present i have an access database, with relational database, consisting of tables - Client, ClientInvoice, ClientOrderDetail and ClientPayment

[finbarrodonovan]

i am using the data environment in vb6 and the data tables are in my last reply

[DaveKeatley]

could you post the db with some data? I may be able to create some queries for you to get the data to a point that the report would be easy to write.

i'd look at it this way with some vageries:

1. do a query to get the client invoice assembled if you don't have a total field in the "clientinvoice" table

2. do a query to combine the invoice and payment info

3. create a report referencing the client data using the CLientid
the running total field would just add the value for invoices and subtract for payments.

[finbarrodonovan]

to be honest i have only starting using vb 6 + the data environment only a few days ago, but i have posted up a summary of the database file, if you could create a few queries, that would be great, anything, just to see something happening.
Thanks
Finbarr