A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

update a table based on a sum query



 
 
Thread Tools Display Modes
  #1  
Old May 22nd, 2010, 06:15 PM posted to microsoft.public.access.gettingstarted
granola911 via AccessMonster.com
external usenet poster
 
Posts: 10
Default update a table based on a sum query

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.

--
Message posted via http://www.accessmonster.com

  #2  
Old May 22nd, 2010, 08:09 PM posted to microsoft.public.access.gettingstarted
Larry Linson
external usenet poster
 
Posts: 3,112
Default update a table based on a sum query

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.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"granola911 via AccessMonster.com" u56971@uwe wrote in message
news:a864c99ae11a5@uwe...
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.

--
Message posted via http://www.accessmonster.com



  #3  
Old May 22nd, 2010, 11:59 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default update a table based on a sum query

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

granola911 wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.