invoice data

  • Thread starter Thread starter cjgav
  • Start date Start date
C

cjgav

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.
 
I would like to create a table with the invoice total & vat in it .
Click to expand...

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.
 
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 ?
 
Doctor said:
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.
Click to expand...

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.
 
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)
 
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.
 
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.
 
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
 
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.
Click to expand...

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 store:

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).
 
IMHO, there are many business reasons to consider an invoice to be an entity
(and a one time event and record of such) rather than just a calculation.
In cases where this concept is used, it is consistent with (and not an
exception to) the rules of DB design to record it, including it's totals.

I believe that the fundamental structure of Northwind does not do the above.
 
If you are storing all the component values, how does storing their sum
(i.e., the "total") add value? And if someone corrects a mistaken entry,
won't this approach require additional procedures to "re-sync" the total
from the components?

(I'm not convinced one way or the other, yet, just trying to flesh out all
the considerations...)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hello Jeff,

I put the "in cases where...." in my post because I think that the concept
in my post is sometimes but not always the best one to use. But an argument
for my "sometimes" thought" would be:

In business, invoices are usually treated as a one time event. (I.E.
something that you must do once and only once). After that you can send a
copy of an invoice (identified as such), or a statement, but never send
the invoice a second time. All of the enterprise softwares that we've
used even serialize (with an inoice number) this issuance and "lock it up".


IMHO your question provides a good example situation. Let's say that you
have issued an invoice, and then a week later you find that a price was in
error. You would issue a new document (typically 2nd invoice with a new
number) with a credit or debit to make the correction. You would not want
to destroy your record of the original invoice, and so you would specifically
NOT want to "resync" the original invoice.


I think that an analogy might be if there was a math contest. And they
asked what 11 times 12 is. Bill answers 132 and wins the contest. They
officially recorded that he answered 132. This is a record of what was
said, (a databasable entity) not just a display of what 11 times 12 is.

A week later the second place finisher says that Bill gave the wrong answer.
You want to prove them wrong. If you did not record the "132", and
instead say "Bill won, and the correct answer for 11 times 12 is 132,
therefore Bill must have answered 132" the next thing that they will say is
"so, you have no record of how Bill answered the question"

The down side of the "invoice as an entity" concept is that databasing
invoices as entities it makes it a lot more complicated. So complicated
that from what I've seen it is seldom done in Access, although routinely done
in in enterprise software products.
 
OK, that makes good sense.

We have a similar situation in that we create contract/amendment documents,
which we do not wish to have altered (unless we do the work). We save the
document as a PDF file and send it via email, then we scan the
fully-executed document and store it for any future need. Now if I can just
get them to drop the paper file!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Not fast, not easy, but some of us don't know how to let go once we start
.... I think it goes along with the mind-set of "How Hard Could It Be?"<G!>

Jeff
 
Hi
Thanks for your help .

NG said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top