Calcualting Sum of Invoice items

[pduncan]

I have a table that contains line items from invoices. Some invoices have only one line, while others contain multiple lines. There is a column (tblInvoice.LineNo) that signifies the Invoices line number. There is a column called tblInvoice.InvoiceNo that is the invoice number. Each invoice line actually represents an individual part ordered on the invoice. Each line has many other columns related to the part such as QtyShipped, part price, tax, and freight.

What is the best way to calculate and save to a table, the sum of each invoice? The sum would be (QtyShipped * Part Price) + Tax + Freight ( for every line in the invoice) .

Thanks


PDUNCAN - MEMPHIS, TN

 

[SkipVought]

Hi,

Select tblInvoice, ((QtyShipped * Part Price) + Tax + Freight) 
From tblInvoice
Group By tblInvoice

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

oops...

Select tblInvoice, SUM((QtyShipped * Part Price) + Tax + Freight) 
From tblInvoice
Group By tblInvoice

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[pduncan]

Skip - thanks - I got the select working (had a couple of minor name changes) but how do I save the results back to the table (in a field named tblInvoice.InvTotal)?

PDUNCAN - MEMPHIS, TN

 

[SkipVought]

You use this in a QUERY to display the results from your source data.

It is rarely a good idea to STORE aggregations in a source table.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[pduncan]

I think I understand… Please don’t think I am doubting your answer – because I am not. But why is it bad to save aggregates in a source table? More importantly, what do you do if you really need to save the invoice totals? How would you do that? Could I save the sum aggregates to a different table? Like one that would only save invoice "totals" such as aggregate tax, freight, and total for each invoice?

PDUNCAN - MEMPHIS, TN

 

[SkipVought]

Why bother to save, when a query can give you the results interactively, as the data dymanically changes?

I suppose that you could "Archive" such a summary, ie one row per Invoice. Just change your query to an make table or append query. But that table is NEVER CURRENT except at the moment that the table is updated.

But think about it. You have a sub-set of rows for the same invoice. Would you want to put the SAME DATA IN EACH AND EVERY ROW??? That is NOT a best and accepted practice in database administration. It wastes storage space. It is ambiquous, in that it must NOT be aggrigated,while other columns can be aggrigated.

NOT A GOOD IDES!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lespaul]

not only that, but if someone adjusts the quantity (say they really only order 100, but an extra 0 was added), now the total needs to be updated again when the quantity is corrected. If you just get the information in a query each time, then the invoice total is ALWAYS correct.

Read 'The Fundamentals of Relational Database Design' for more information on normalization and why storing totals breaks third normal form.

Now, there are some situations where you may need to store an item of data and it is redundant. Take the price of the item. You may want to store the price the item was sold in the invoice detail table because the price can change over time and you don't want the new price applied to old sales.



Leslie

 

[pduncan]

OK – I can certainly understand from a normalization perspective why this is a bad idea. And again, I am not arguing with either of you – in fact I really appreciate your input and guidance. However, let me tell you the “whole” story and then please tell me what you think I should do:

I work for a large service based company. We have several field locations that order and install parts from a large manufacturer. Due to our size, we receive a national buying discount – however the manufacturer wants to hide these discount prices from the market. Therefore I receive a spreadsheet from the manufacturer that details the invoice items (at the national cost). My application imports the spreadsheet, looks up the regional cost (part ABC may cost less in one city vs. another) and saves the regional cost. Also, the tax rate is calculated – and then the “regional” tax is applied.

Essentially, a “regional” invoice is generated for each “national” invoice received. The regional invoice is sent to the general manager of the field location for approval.
I need to then generate a file that is passed to our accounts payable system that contains the financial data:

One line is a debit (at regional price) to the locations accounts payable.
Another line is a debit for the difference between national and regional cost to a corporate account that gets summed each month and credited back to the service center monthly.

I was saving these values because I thought it would be easier for me to extract these values later – and because I know that these values are static. I have read (many times before it started to click) the Relational Fundamentals paper – and I can agree with your arguments, however – do it ever make sense to save denormalized data, as in my situation?


Thanks, PDUNCAN

 

[SkipVought]

I did not see your point at all. All conpanies have data that is proprietary or sensative and not to be viewed. That has NOTHING to do with your situation.

You should only display or publish the data that you want certain people to see, rather than destroy of contaminate your data structure.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}