If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
invoice data
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? |
#3
|
|||
|
|||
invoice data
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] |
#4
|
|||
|
|||
invoice data
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] |
#5
|
|||
|
|||
invoice data
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 |
#6
|
|||
|
|||
invoice data
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] |
#7
|
|||
|
|||
invoice data
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? |
#8
|
|||
|
|||
invoice data
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. |
#9
|
|||
|
|||
invoice data
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 |
#10
|
|||
|
|||
invoice data
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/ |
|
Thread Tools | |
Display Modes | |
|
|