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.
--
Ken Snell
<MS ACCESS MVP>
"Avaya" <(E-Mail Removed)> wrote in message
news:2C0761FC-AB79-4A43-ACF2-(E-Mail Removed)...
> 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)" wrote:
>
>> 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.
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Craig" <craig@icarmitageditcodituk> wrote in message
>> news:1EF2E070-8A97-4ADA-9913-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>
|
Similar Threads
