Re: create totals based on a field values...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It might be a big issue because You are asking for advice on
how to do something that has yet to be fully explained to me
(or others). In general an invoice is no more final than
any other data. What happens if a customer calls in to
complain that the invoice is inaccurate and you agree with
them? Most likely, the invoice should be edited to make it
correct and the total you have stored must be recalculated
to bring them in line with the changes, right? The usual
way to deal with this is store the quantity and price values
for each item in the invoice details and to just calculate
the invoice total whenever you need to see it. The balance
due amount is another calculation that needs to be done on
the fly (for similar reasons). This is why you would use a
query, form or report calculations to display those values
as needed.

As for recording the receivables as they come in, you only
need to know the invoice number while making entries in the
Payments table. This would commonly be done by just
entering the invoice number in its field in the payments
form or using some other kind of mechanism to select the
payment's matching invoice number. The totals are not
needed at this point.

To generate an updated statement for the customer, you would
run a report that performs the needed, up to date,
calculations.

And, no, I don't believe you are thinking irrationally. I
just think all the issues have yet to be fully explained.
--
Marsh
MVP [MS Access]


Brook wrote:
> I don't know if I am just not thinking of this right, but I don't
>understand why this is such a big issue? Once my invoice is created, it
>doesn't change therefore the totals don't change...
>
> That is why I was trying to set up a table or query to accept the
>totalinvoice amount and build from there to accept payments so that I can
>create a report showing invoice total less payment 1 less payment etc equals
>Balance Due, and I could send that Balance Due Report to my clients..
>
> Am I just thinking irrationally as far as codeing aspect ms access?
>
>
>> Brook wrote:
>> > what I am ultimately trying to accomplish is to setup a completely
>> >independent Accounts Receivables table/query for my invoices. The reason is,
>> >is that I don't want to have to go into each of my invoices/frminvoices to
>> >accept payments. B/c on any given day I might receive 50 payments and that
>> >would be 50 times that I would have to go into 50 different invoices.
>> >
>> > So I am trying to setup an indepent qry that will calculate and store my
>> >invoicetotals so I can create a form to have the opportunity to enter
>> >multiple payments to multiple invoices at any given time... if you can give
>> >me a better method... I am open.
>>
>>
>"Marshall Barton" wrote:
>> Oh boy, that's a different kind of operation than I thought
>> you were asking about. Since a Totals type query like I
>> posted before is not updatable, it con not be used to
>> edit/add data to your database. It would be fine to use to
>> display the totals on the screen or in a report, but not in
>> an editable form.
>>
>> To be able to enter payment information, you need to use a
>> form based on the Payments table. You may(?) already have
>> this somehow, but I don't know how your entire application
>> is designed.
>>
>> As for the totals you were trying to calculate in a query,
>> that may, or may not, be useful to know when just entering
>> the payments data, but those totals should NOT be stored in
>> a table.
>>
>> It seems to me, that the only info about an invoice that
>> would be needed when entering a payment is the invoice
>> number. On the other hand, it would also be viable to enter
>> payments from a customer without caring which invoice(s?)
>> the payment is applied to.
>>
>> From where I sit, it looks like you need to step back from
>> the query design and think more deeply about specifying the
>> entire operation before proceeding with an implementation.
.

Relevant Pages

  • Re: =iif Calculated field in report?
    ... John Spencer has started me well in the right ... payments field, I get the total just right. ... you asked about the form fields and the report. ... I have an invoice report with three sub reports: ...
    (microsoft.public.access.reports)
  • Re: =iif Calculated field in report?
    ... First, thank you John! ... payments field, I get the total just right. ... you asked about the form fields and the report. ... I have an invoice report with three sub reports: ...
    (microsoft.public.access.reports)
  • Re: Group Totals
    ... Now it souds like it just a matter of formatting the report ... OTOH maybe you are struggling with calculating the invoice ... I want to be able to display this ... >>>the next page and attempts to display my totals, ...
    (microsoft.public.access.reports)
  • Re: Getting Totals From Two Different Tables In A Query
    ... All records from the invoice query, ... This handles the case where a client has invoices but no payments. ... > I'd like to get the totals of the payments and of the charges for each ...
    (comp.databases.ms-access)
  • Re: create totals based on a field values...
    ... Once my invoice is created, ... totalinvoice amount and build from there to accept payments so that I can ... Since a Totals type query like I ...
    (microsoft.public.access.queries)