invoice data

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

"Fred" wrote in message
...
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.

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

"Fred" wrote in message
...
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.

Cool.

It would be beyond me to design the Access app to do that but I'm sure that
you could.

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

"Fred" wrote in message
...
Cool.

It would be beyond me to design the Access app to do that but I'm sure
that
you could.

Great Discussion. Challenged my thoughts. Gave some ideas. Thanks.

"Doctor" wrote:

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 13 Jul 2009 20:53:12 GMT, "David W. Fenton"
wrote:

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

Thanks, David - good points.
--

John W. Vinson [MVP]

Hi
Thanks for your help .

"NG" wrote:

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.