Creating a customer accounts statement vis query?

[Craig]

Hi, i use access to create invoices for jobs that i do.. currently i have
the following...

Jobs database that includes a the cost for the job. I print an invoice from
this information... The layout is basically the following..

job.customerID
job.description
job.date
job.charge

I also record payments in a separate database called Payments because the
payments are sometimes different than the charges...

payment.customerID
payment.date
payment.amount

I would love to create a report that uses the data from both of these tables
and generates a statement like so...

Date Credit Debit
1/2/07 £50.00
2/2/07 £40.00

Total Outstanding £10.00


I am assuming the best way is to create a query that pulls data from both
tables but even though i think i understand the concept... i dont know how i
would get access to do this... can someone help please!


thanks in advance

Craig

 

[Ken Snell \(MVP\)]

This appears to be an easy thing to do, but it actually is much more
complicated than you might first think.

You need to denormalize the charge and payment data via queries and put
those data where you can read them for your report (via another query). You
need to get a list of the dates that correspond to the payments and charges
(leaving out all other dates) and put them where you can read them for your
report (via a query). You then must combine these data into the "statement"
appearance that you want in the report.

Although it's possible to do all this by just queries, the report will
run/view/print very slowly if you have more than just a few data items; I
state this from personal experience in an application that I've written that
does statements. Therefore, I found that the best method is to use temporary
tables to hold the denormalized data and the date data, where I can index
the field(s) in the tables as needed to make the final query run faster, and
then use those tables in the final query to make the report.

If you want to pursue this, post back and we'll walk through it one step at
a time. It'll take a number of post exchanges to finish it up.

 

[Guest]

Hi Ken,
Can you walk me through the step in creating a customer accounts statement
based on the following table structure? Thank you.

tblSuppliers:
VdrID PK
Vendor

tblOrders:
VdrID FK
POnbr PK
PO Amount

tblPO Change
POnbr FK
Date Amended
Increase Amt


tblInvoice
POnbr FK
VdrID PK
InvNbr PK
InvDate
InvAmt

tblPayments
VdrID FK
InvNbr FK
DatePaid
AmtPaid

I need to calculate the balance of each suppliers open invoice and also the
available balance of each PO amount.

 

[Ken Snell \(MVP\)]

I'll need more detailed explanation of what the data fields are holding as a
start, and what is meant by "open" for an invoice, and whether the statement
is to be for a single PO or for all POs for that supplier.