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

0
granola911
5/22/2010 5:15:38 PM
access.gettingstarted 618 articles. 1 followers. Follow

2 Replies
1143 Views

Similar Articles

[PageSpeed] 46

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
> 


0
Larry
5/22/2010 7:09:57 PM
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/Forums.aspx/access-gettingstarted/201005/1

0
KenSheridan
5/22/2010 10:59:35 PM
Reply:

Similar Artilces:

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

want to do piovt table, where rows over 100,000
Hi.. i have around 100,000 rows of data... of cousres the worksheet i can only put 66000 or so rows per sheet. i want to do a pivot table that combines the two sheets. how can i do this? thank -- Message posted from http://www.ExcelForum.com Hi if you have that many data I would suggest you store this data in a real database (e.g. MS Access). With Excel's pivot table wizard you can then access this database as datasource. Another way would be to split the data in two worksheets and within the pivot table wizard choose the 'non contigenous ranges' for the data source -- Reg...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Formulas not updating ?
I have a problem with my spreadsheet under Excel XP. The problem has to do with cell references not updating when a row is inserted into the worksheet. The formulas in the cells are relatively simple: =B13 and =DATE(YEAR(B10),MONTH(B10),DAY(B10)+7) I can copy a row and insert it elsewhere or just insert a blank row and the cell references in rows below are not updated in all cases. It seems that rows that are separated from the inserted rows by a blank row are updated. Is this a known issue or does anyone have any suggestions on how to fix this? The spreadsheet is intended to p...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

windows update 12-25-09
I believe ninety nine percent of the updates failed on my computer. In fact I am unable to download IE8, and several other programs. I am running an IE7 in my computer and I am getting the warning that my os does not support it. What is my operating system? Windows vista basic home, is'nt funny!... Harry Dupre wrote: > I believe ninety nine percent of the updates failed on my computer. > In fact I am unable to download IE8, and several other programs. I > am running an IE7 in my computer and I am getting the warning that > my os does not support it. > > Wh...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

Update another CPropertySheet page
I have a program which contains 3 CPropertySheet pages. When I add the change the value in Page 1, then it should automatically update a corresponding field in the Page 2. How can I access from one page to another? Thanks. Yan yan wrote: > I have a program which contains 3 CPropertySheet pages. When I add the > change the value in Page 1, then it should automatically update a > corresponding field in the Page 2. How can I access from one page to > another? > > Thanks. > > Yan > > You cannot do that. When you change a value in Page 1 there are cases ...

Query Problem in Test.
I keep getting errors on the WHERE part of my query. I'm sure it's something simple. I don't know if you will need the whole code to see what the problem is. It is lengthy, so I will start with just where the problem is. Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM (Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID = qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5 = T2.ZIPCode WHERE " & strWhere) ' See if found none If rs.RecordCount = 0 Then MsgBox "No Churches meet your crite...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question and based on their response (whether they type yes or no) I want the query to check the field and return all records marked yes is they type yes and all other records if they type no. How can I do this? Also, could I present them with a simple text box (having yes and no choices) or maybe a check box so they won't have to type anything? If you help me with the first part, this question is a bonus. I'll be happy with just the first question answered. Thanks! A Yes/No field actually stores -...

Automatic update of information in a spreadsheet
Hi, I am using Microsoft Excel 2000. I have just done a spreadsheet which contains information about patients. In one worksheet I have the raw data and then in others I have copied columns across and then used IF(AND) statements to abstract the information I need, e.g. to work out how many males there are in each of the hospitals. What I need to know is how do I set it up so that when a new entry is added to the raw data, i.e. another patient in a new row, it automatically updates the information in the other worksheets? What is happening at the moment is when I add another row the IF s...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

Show / Hide items in Pivot Table as required
Hi I am using MS Excel 2003 and need a macro code for show only my selected items only in a Pivot Table. Query: One excel table empstatus.xls in which fields are Employee No , Department, Status, other fields. In status field items are Blank, Leave, Left, Resigned. I wanted to show only blank & leave based on when I’m selecting a particular month in my pivot table. So is there any code to hide all data items of status and only show blank & leave. Please suggest. -- ------------------------------ Thanks Nitesh ------------------------------ ...

Query is making a nuts
Hi Using Access 2007 I have a table and two of the fields (Status and OrigStatus) have, among other possible entries, the words "Member," "Customer" or "Request." I am trying to create a query that only displays records which do NOT have "Member," "Customer" or "Request" in either field. But it won't work! I have tried putting each word in its own Criteria column in each field using syntax such as: <>"Member" with no success. I have also tried <>"Member" OR "Customer" OR "...

Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my boss loves tabs for navigation). Each page has a list box based on a category of training events that members of my office attend and the box is based on a query that selects the training events for the tab page's respective category. I select an item from the list, click a button, and go to a form with a text box showing the name of the training event I previously selected. On that form, I want to select mutiple names of office members from a list box (already created and source is the Office Roster table) and then c...

Email link to update Outlook --- Calendar
Anyone knows how to send an link within an email, inwhich the Recipient can click to have the their Outlook "Calendar" automatic updated with appointment information. I have seen this work, can't find inforamtion on how to create this link. Thanks ...

CMap query
Hi, I have a text file and i read the complete file and build a FILELIST - Files & GROUPLIST - groups like odbc,,jet40, jet35 The Group list in the file consists of values like version | odbc | 430 version | Jet40 | 430 version | Jet35 | 430 version | oledb | 430 version | odbc | 440 version | Jet40 | 440 version | Jet35 | 440 version | oledb | 440 The File List consists of values like FILE | odbc | <COMPLETE path of odbc file like odbc32.dll> FILE | odbc | <COMPLETE path of odbc file like odbcji32.dll> FILE | oledb | <COMPLETE path of oledb dll1> FILE | ole...

Global Address List not updating #3
Hi, We have SBS 2003, and last week I added various Distribution Groups. I added them in the Active Directory and Users tool and they appear there and under Distribution Groups in the Server Managment. However in Outlook, non of these distribution groups appear in the Global Address List. Anyone have an idea what could be wrong or how to force the Global Address List to see the changes? Thanks! What clients are you using? Have you tried a rebuild in ESM? Nue "Lee" <noreply@infinityconcepts.net> wrote in message news:uOC0kHMXGHA.752@TK2MSFTNGP02.phx.gbl... > Hi...