update a table based on a sum query

[granola911 via AccessMonster.com]

Hi All,

I have seen the zillions of answers to this that state "never add a total
into the table.." But there are cases, such as mine when it is prefered.

I want to update a project table with the final invoice cost. I want it
entered as a hard figure so that I can then export the table for accounting
purposes. Additionally, once an invoice has been generated, there isn't any
need for the total to ever, ever change! In fact it shouldn't.

So the question is.... is there a way to do this?

To update a table with a value from a sum query?

Thanks for any help.

 

[Larry Linson]

Unfortunately, you have misunderstood the "zillions of answers". They
contend that it is unwise to store totals in a table that can be generated,
when needed, from detail data in the table. That does not apply to what you
want to do.

It is perfectly OK to store a total in a table if it is a one-time,
never-after changing, value.

As you have not described your data, or the layout of your tables, I can
only offer some general guidance. If you have one table, say Project, that
describes the project, and other records with project-related information,
say Project Details, you have two choices: add a field in the Project table
for "InvoiceValue"; or add a Project Detail record, identifying the detail
item as "Invoice Value", with a numeric field for the value.

 

[KenSheridan via AccessMonster.com]

A computed value should only be stored at a column position in a row in a
table if the value from which the computed value is derived can change over
time, but the computed value needs to remain static. This would be the case
with an invoice total if the invoice total is computed from unit costs which
can change, and only the invoice total is stored. The total will therefore
remain static notwithstanding the inevitable changes in the unit cost per
product.

If in the other hand the unit costs per line item are stored in rows in an
invoice details table then the invoice total should not be stored, nor is
there any advantage in doing so. On the contrary, there are inherent dangers
in doing so because there is nothing to stop the invoice total being changed
so that it is inconsistent with the aggregated line item costs for the
invoice in question, or vice versa.

The latter scenario is generally the case in my experience as it is usual for
an invoice to be itemised rather than simply presenting the total invoice
amount.

As regards your point about the need to export the data for accounting
purposes this does not require the total invoice amount to be stored in a
base table as the result table of a query which computes the invoice total
from the line item costs can equally well be exported. Using Northwind as an
example the following extension of its Invoice Data query by the inclusion of
a subquery adds the invoice total to each invoice detail row returned:

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.
[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code],
Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company AS
[Customer Name], Customers.Address, Customers.City, Customers.[State/Province]
, Customers.[ZIP/Postal Code], Customers.[Country/Region], [Employees
Extended].[Employee Name] AS Salesperson, Orders.[Order Date], Orders.
[Shipped Date], Shippers.Company AS [Shipper Name], [Order Details].[Product
ID], Products.ID AS [Product ID], [Order Details].[Unit Price], [Order
Details].Quantity, [Order Details].Discount, CCur(Nz([Unit Price]*[Quantity]*
(1-[Discount]),0)/100)*100 AS ExtendedPrice, Orders.[Shipping Fee], Products.
[Product Name],
(SELECT SUM(CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100)
FROM [Order Details] As OD2
WHERE OD2.[Order ID] = [Order Details].[Order ID]) As [Invoice Total]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
= Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN (
[Order Details] LEFT JOIN Products ON [Order Details].[Product ID] = Products.
ID) ON Orders.[Order ID] = [Order Details].[Order ID];

While the following adaptation of the query to an aggregating query returns
one line per order with the gross order total:

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.
[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code],
Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company AS
[Customer Name], Customers.Address, Customers.City, Customers.[State/Province]
, Customers.[ZIP/Postal Code], Customers.[Country/Region], [Employees
Extended].[Employee Name] AS Salesperson, Orders.[Order Date], Orders.
[Shipped Date], Shippers.Company AS [Shipper Name], Orders.[Shipping Fee],
SUM(CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100) AS [Invoice
Total]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees
Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID
= Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN (
[Order Details] LEFT JOIN Products ON [Order Details].[Product ID] = Products.
ID) ON Orders.[Order ID] = [Order Details].[Order ID]
GROUP BY Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.
[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code],
Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company,
Customers.Address, Customers.City, Customers.[State/Province], Customers.
[ZIP/Postal Code], Customers.[Country/Region], [Employees Extended].[Employee
Name], Orders.[Order Date], Orders.[Shipped Date], Shippers.Company, Orders.
[Shipping Fee];

Ken Sheridan
Stafford, England