invoice data

Hi
I've created an invoice based on northwind.
I would like to create a table with the invoice total & vat in it .
Because this is generated in the invoice report I d'nt seem to be able to
enter in a table.
can anyone help?

You shouldn't store a calculated value in a table. Calculate what your
looking for either in a query, form, or report when they run.

"cjgav" wrote:

Hi
I've created an invoice based on northwind.
I would like to create a table with the invoice total & vat in it .
Because this is generated in the invoice report I d'nt seem to be able to
enter in a table.
can anyone help?

On Sun, 12 Jul 2009 15:46:01 -0700, cjgav
wrote:

I would like to create a table with the invoice total & vat in it .

Probably not, actually.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
--

John W. Vinson [MVP]

Hi
Thanks for the replies.
once the invoices have been printed and sent to customer I dont want them to
change i then need to store the invoice total and vat as unpaid then paid
when its paid for. Whats the best way to do this please ?

"John W. Vinson" wrote:

On Sun, 12 Jul 2009 15:46:01 -0700, cjgav
wrote:

I would like to create a table with the invoice total & vat in it .

Probably not, actually.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
--

John W. Vinson [MVP]

In message , Doctor
writes
You shouldn't store a calculated value in a table. Calculate what your
looking for either in a query, form, or report when they run.

WRONG!

Invoice totals are a very well-known exception to the rules of
normalisation. You should always store the total value rather than
recalculating it each time. If the unit-price changes after the invoice
has been issued your calculated value will not agree with the invoice
value. Depending on your audit requirements you may also want to store
the unit-price and any taxes charged.

If your invoicing program doesn't leave an audit trail that shows how it
calculated the invoice values then you need to add one. I would create
an append query that adds all of the line-item prices and taxes to a
table then sums them and appends the totals to a separate table. I would
then use these tables to produce the invoices. Watch out for rounding
errors. The total value of the invoice should match the total of the
printed values for the line-items.



--
Bernard Peek

Hi,

normally you have two tables to store the invoices: invoice header and
invoice details. You always store the sales price of every item as it is on
the invoicing date separate in the details table. Most invoicing packages
store the invoice total in the header table, because for a lot of operations
you don't want to bother with the details. Another reason for is is that
legally the invoices have to be stored as they were printed, with rounded
figures or (hopefully not) errors and all.
TVA is always calculated on the rounded total (general rule: store the
figures rounded to 3 decimals and show them formatted to 2)
--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thanks for the replies.
once the invoices have been printed and sent to customer I dont want them to
change i then need to store the invoice total and vat as unpaid then paid
when its paid for. Whats the best way to do this please ?

"John W. Vinson" wrote:

On Sun, 12 Jul 2009 15:46:01 -0700, cjgav
wrote:

I would like to create a table with the invoice total & vat in it .

Probably not, actually.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
--

John W. Vinson [MVP]

Hi
Thanks for your Help.
Were I seem to be stuck is I cannot display the sum of cost of parts used in
a query or report , it seems easy to do on a report (=sum[fieldName]) But
this does not work in query as there is a record for each part used.

"cjgav" wrote:

Hi
I've created an invoice based on northwind.
I would like to create a table with the invoice total & vat in it .
Because this is generated in the invoice report I d'nt seem to be able to
enter in a table.
can anyone help?

Hi,

In the query design you need to build a totals query. Use the button with
the sigma sign to transform your query in a totals query, then group on the
fields you need to group on and set the sum for the fields you want to
display the totals for.
--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thanks for your Help.
Were I seem to be stuck is I cannot display the sum of cost of parts used in
a query or report , it seems easy to do on a report (=sum[fieldName]) But
this does not work in query as there is a record for each part used.

An alternative to storing the unit-price-at-time-of-sale for each/every
product sold would be to use a pair of date-range fields in the product
table. That way, you can have the same product at different points in time,
with different unit prices. This provides a history of the products' price
changes, and a mechanism (via looking up) for reconstructing the
"price-at-sale".

So, perhaps not "WRONG!", so much as a possible exception to the general
approach that suggests not storing calculated values.

(and I'm dealing right now with the aftermath of some well-intention design
that DID store a calculated value, AND has a current unit price connected to
the product. The problem? -- some of the folks who DON'T understand the
data are trying to use the current unit price to reconstruct the
"price-at-sale", rather than using the "extended amount" and dividing by
quantity to derive unit-price-at-sale.)

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Bernard Peek" wrote in message
...
In message , Doctor
writes
You shouldn't store a calculated value in a table. Calculate what your
looking for either in a query, form, or report when they run.

WRONG!

Invoice totals are a very well-known exception to the rules of
normalisation. You should always store the total value rather than
recalculating it each time. If the unit-price changes after the invoice
has been issued your calculated value will not agree with the invoice
value. Depending on your audit requirements you may also want to store the
unit-price and any taxes charged.

If your invoicing program doesn't leave an audit trail that shows how it
calculated the invoice values then you need to add one. I would create an
append query that adds all of the line-item prices and taxes to a table
then sums them and appends the totals to a separate table. I would then
use these tables to produce the invoices. Watch out for rounding errors.
The total value of the invoice should match the total of the printed
values for the line-items.



--
Bernard Peek

John W. Vinson wrote in
:

On Sun, 12 Jul 2009 15:46:01 -0700, cjgav
wrote:

I would like to create a table with the invoice total & vat in it
.

Probably not, actually.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

I would disagree. Invoice calculations are one thing that perhaps
should *not* be calculated on the fly.

First off, you need to record the VAT/tax percentage or amount,
rather than just storing a pointer to a tax rate lookup, because the
rate can change.

Secondly, because you're dealing with floating point operations, I
think it's better to store the rounded results of calculations,
rather than calculating them on the fly.

Thus, I would sto

1. invoice subtotal (before tax), appropriately rounded.

2. tax amount, appropriately rounded.

There is no need to store the total of those two fields, but I think
it is valuable to store both, as calculating the subtotal for large
numbers of invoices can be a real performance drain.

This is the voice of experience speaking here, not theory. I have an
app that went into production use in early 1998 that now has over
100K invoices. Calculating customer balances on that many invoices
without storing subtotals would be crippling (indeed, it *was*
crippling until I rewrote the app to store the subtotal).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/