How to create a field in a table whose value will be calculated

I could use some help in creating a field in a table the value of which would 
be the continuing balance of simple arithmetic operations such as:

T(n) = T(n-1) A(n) - S(n) where T=Total, A=value in a field to be added, 
S=value in a field to be subtracted, and n=record number

Does this make sense? 
0
Utf
3/16/2010 4:38:01 AM
access 16762 articles. 3 followers. Follow

2 Replies
970 Views

Similar Articles

[PageSpeed] 6

Slipperyrock,

If I understand you correctly you want a calculated field in a table... 
that would be a no can do BUT you can do it in a query.  Now, you can 
simulate a table view using a query and a form.

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Slipperyrock" <Slipperyrock@discussions.microsoft.com> wrote in message 
news:D3DDD7C0-A256-4057-A7BB-415D48C257E0@microsoft.com...
I could use some help in creating a field in a table the value of which 
would
be the continuing balance of simple arithmetic operations such as:

T(n) = T(n-1) A(n) - S(n) where T=Total, A=value in a field to be added,
S=value in a field to be subtracted, and n=record number

Does this make sense? 

0
Gina
3/16/2010 4:58:38 AM
On Mon, 15 Mar 2010 21:38:01 -0700, Slipperyrock
<Slipperyrock@discussions.microsoft.com> wrote:

>I could use some help in creating a field in a table the value of which would 
>be the continuing balance of simple arithmetic operations such as:
>
>T(n) = T(n-1) A(n) - S(n) where T=Total, A=value in a field to be added, 
>S=value in a field to be subtracted, and n=record number
>
>Does this make sense? 

No, not in a relational database.

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 in the control source of a Form or a Report
textbox.

And there *are* no "record numbers" in a relational table, other than record
numbers that you create and maintain yourself.
-- 

             John W. Vinson [MVP]
0
John
3/16/2010 5:22:38 AM
Reply:

Similar Artilces:

How to create buttons at runtime?
Hello! My application is connected to a database. Now I want to visualize some of the datasets. Easyest way seems to be using buttons. I don't know the number of datasets at startup, so I have to create the buttons in a loop at runtime. Does anybody have an idea or source? I am using Visual C++ 6.0. Thanks a lot, Mirco Mirco Schmedicke wrote: > > Hello! > > My application is connected to a database. Now I want to visualize some of > the datasets. Easyest way seems to be using buttons. I don't know the number > of datasets at startup, so I have to create the bu...

Subtotal not calculating fully
I have a simple function to subtotal a long column. The function is =SUBTOTAL(9,P7:P850) some of the cells in the column have values but don't add to th total. Any help would be appreciated -- Message posted from http://www.ExcelForum.com Some of the values are most likely entered as Text. Coerce them to numbers by selecting an empty cell, copying it, selecting your numbers and choosing Edit/Paste Special, selecting the Values and Add radio buttons. In article <marvontherim.17zcdw@excelforum-nospam.com>, marvontherim <<marvontherim.17zcdw@excelforum-nospam.com>>...

MoveCopy, Unable to create dest.msg
Hi all, I have a user with this message "MoveCopy, Unable to create dest.msg". I have searched the newsgroup and have found others with the same problem, but no resolution. Does anyone have any ideas as to how to go about resolving this?? Thanks in advance Andy ...

Cannot Delete rows in AsyncOperationBase table (MSCRM DB is 73GB on SBS)
In my ignorance, I let the MSCRM database grow to a huge size. I became alarmed at 58GB and 2 days later it was 73GB. With the help of my solutions partner, we found out that we have to manage the size of my AsyncOperationBase table by deleting old completed rows. The scripts always timeout. Then I decided to try and delete just one row using SQL Management Studio. Here is the error. No rows were deleted. A problem occurred attempting to delete row 1. Error Source: .Net SqlClient Data Provider. Error Message: Timeout expired. The timeout period elapsed prior to completion of the operati...

What is best way to determine whether a value exists in a column of values?
Excel 2002 Hi What is the best way to lookup a value (i.e. a number) to see whether or not it is in another column of values? I need exact matches only I cant get lookup() to work... With thanks Ship Shiperton Henethe Strangely enough I have now discovered that the function =countif() seems to work quite well! Ship Shiperton Henethe ...

Deleting unicode fixup table
One of our clients has an Exchange 2003 Std SP1 installation that just bumped up against the 16Gb limit. We had them do some house cleaning and did the 17Gb hack to get them temporary extra space which got the system back on an even keel but when we tried to do the offline defrag to compress their database the result showed us it was corrupt and failed about 10% into it. The store mounts and runs fine and backs up OK but we are unable to defrag and now we have concerns that bad things are on the horizon. I know everybody says the best thing to do is a restore from b/u if at all possible and ...

automating a pivot table
When using a pivot table, is there any way of automatically selecting from a drop down menu. By this I mean if I can select one of 5 colours, If I put "red" in cell A1, can the pivot table be made to take the value in cell A1 as the colour selection, rather than me selecting red from the drop down list. thanks in advance You can add fields to the page area. When you select from the dropdown list in a field there, the table is filtered to show only that item. bobf wrote: > When using a pivot table, is there any way of > automatically selecting from a drop down menu. &g...

Using Form to update table
Hi I am a little lost. I have a form which has a subform. In the header of the form there is a combo box to selete numbers and a text box to enter a date (not current date) On the subform I have 2 text boxes which onces the 2nd textbox is populated another set is to come underneath ready for completion (continues form). On the main forms footer I have an update button which is to perform a task. What I am lost with is how can I get the subform to work as a continues form so once the 2nd text box is completed another set is ready for entering. and How do I then click on the button to fi...

exchange user mailbox not created
Hi guys, Have exch 2003 on win2003 svr in new AD forest. New user is created and "Create exchange mailbox" is selected for new user. Try to activate mailbox from outlook but name is not listed in GAL. Have restarted RUS, - no change. Failing an exchange server restart is there any way to tell AD / exchange to get its act together and create the mailbox. The properties of user does not display an smtp email address. Any help would be greatly appreciated. Regards Peter pls following the KB 286356 http://support.microsoft.com/kb/286356/en-us -- Jammyù�ٴ� "cumis...

Code creating
I've this code in my app, it creates a toolbar button by button (for now, the toolbar just have 1 button) TBBUTTON tbButton; tbButton.fsState = TBSTATE_ENABLED; tbButton.fsStyle = TBSTYLE_CHECK; tbButton.idCommand = ID_COMMAND tbButton.iBitmap = 0; m_myToolBar.GetToolBarCtrl().AddButtons(1,&tbButton)); // returns non-zero, checked This last line is returning First-chance exception in MDISample.exe (KERNEL32.DLL): 0xC0000005: Access Violation. In the Debug Output (VC++ 6, SP5). The toolbar is created anyway, in Debug and Release, and works fine, but this exception bother...

Pivot Table based on external (.mdb) database
Can you point me to an article/tutorial regarding creation of Pivot Tables based on imported .mdb data files? Thanks! Hi, Try http://edferrero.m6.net/tutorials.html Ed ferrero "KG" <KG@discussions.microsoft.com> wrote in message news:12CA98CF-C52D-45BD-AF7B-7EC527C5FB18@microsoft.com... > Can you point me to an article/tutorial regarding creation of Pivot Tables > based on imported .mdb data files? > > Thanks! Perfect! I'll need to do a little studying... "Ed Ferrero" wrote: > Hi, > > Try http://edferrero.m6.net/tutorials.html &g...

Powershell script to create Resource Rooms Exchange 2007
I am attemting to make a script to create room accounts via powershell that will set the appropriate -mailboxcalendarsettings depending on the user input. Everything works fine if I put a 5 minute pause between new-mailbox and set-mailboxcalendarsettings. It is like the mailbox isn't fully created, get-mailboxcalenarsettings gives an error "cannot open mailbox" for the first 3-5 mins after creation. Is there a way to tap the mailbox via powershell to speed up the process? Or a setting I can change on the server? Any help or ideas are appreciated! ...

Combining two fields into one in a query for a report
The database I am working on is to keep track of parts that are either in their storage area (on a shelf) or on an assembly line. I have two fields, one called Shelf and every part has a shelf assigned to it. The other field is Line and if a part in on an assembly line, there is a number indicating that line, if the part is on it's shelf, the word STOCK appears in the line field. (Has to be this way for the rest of the database to work, so this is not up for question) Now, I need to creat a report to show where all parts are. If they are on a line the result needs to show the l...

Excel 2007 will not start.... all of a sudden.. pls help
Hi, I have Office 2007... All modules were working fine on clicking icons of word,access,excel etc. All of a sudden to day I can not start.. excel.. other programs .. starts. on starting excel. clicking Icon... I get message as follow. " This file does not have a program associated with ith it for performing this actoion. Create an association with set association control Panel. " Also when With window explorer I go in office12 folder clicking excel EXE file doe not start error message this is not win32 file. can somebody help.. or do I have to reinstall Office 2007 ...

tables that link manufacturing to SOP
Hi All, I have recently joined a company that is using the manufacturing module in Great Plains Dynamics. While I have very familiar with all the standard tables ( especially the SOP30200 and SOP30300) having designed many Crystal Reports for them, I cannot seeme to find the correct way to link an order to a manufacturing order. I have found the IS010001 - ICON Sales Order Line, and the IS030001 - ICON Sales Order Line History files. The issue I am having is that these 2 files seem to replicate not only the manuafactured item, but also any other lines on that order. So am I using th...

Excel 2002 Pivot Table Protection
I've been playing around with protecting my pivot table - so far, I can't get it quite right. What I'm hoping to do is allow users to refresh the pivot table and update any of the "page" dimensions, but NOT allow anyone to pivot or manipulate the row and column dimensions. Is this possible? You could use programming to restrict the pivot table use. For example: '========================================= Sub RestrictPivotTableExceptPage() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) With pt .EnableWizard = False .EnableDril...

simulating columns in rtf field on report
I am creating a system using Access 2007. I have a report which currently displays multiple RTF fields. Within one of the fields, I would like to print a 4-column 4-row table (including borders) to display some data. I need to do this in VBA as the data will vary. Does anyone know if this is possible. Even if I could get the data into a column format (without the borders), that would be an achievement. Any ideas would be appreciated. ...

Created By SYSTEM
Does anyone know why we have activities that are created by users via the outlook client that are showing up as Created By SYSTEM if you do an advanced find and include that field? I have seen this happen when the name in your data does not match. For us we pulled names of our account reps from our SAP data and often that name does not match with the active directory name, so the system struggles to identify records and will assign records and activities that it can not determine the correct owner to System or CRM System. "Dan Stein" wrote: > Does anyone know why we have a...

Compare two table
Hi, I had transfer a table from the server database to my local computer. Is it possible if use excel to compare those two tables and extract the missing records that were not copy out? regards, ray -- ray5_83 ------------------------------------------------------------------------ ray5_83's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29800 View this thread: http://www.excelforum.com/showthread.php?threadid=495113 Maybe... Can you pick out a key field/column that is common to both? If you can, you can use some of the techniques that Chip Pearson descr...

Display Color Hex Values in a DropDownList
How can I populate a DropDownList with the Available Color Hex Values while displaying the Color? I'd like to allow my users a way to set a color profile. Thanks Hello, The problem being exactly ? Is this to get the hex values or to display a color for each item or to enumerate available colors ? You also have ColorDialog (http://msdn.microsoft.com/en-us/library/system.windows.forms.colordialog.aspx)... -- Patrice "Dave" <Dave.Burkett@Jacobs.com> a �crit dans le message de news:f8e3e0b5-1b54-49b4-85db-079e8ea7d2bf@19g2000yqu.googlegroups.com... ...

Investment Account will not Delete
Hello, I have an ING account that I no longer use and is closed now. If I try to close or delete the account when I come back into Money another appears. I now have several ING Investment Accounts listed in Money (i.e. ING Direct Investment #1, #2, #3, etc) due to my multiple attempts to remove or even close so it would not show up. Any idea on how to fix this bug in Money 2007 Premium. My version is 16.0.125.1303. Thanks for any help in advance! In microsoft.public.money, Steven wrote: > >I have an ING account that I no longer use and is closed now. If I try to >c...

Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1 to be the value of the selected cell so that if A1 is selected, C1 will equal red, if A2 is selected, C1 will equal white, etc.? Thanks, Sheldon Potolsky Sheldon, In a word, no. No worksheet thing comes to mind that works as a function of the active cell. You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox) instead of cell selection. They'll let you click to make such choices, and you can use formulas that will change a cell value as you wish. -- Regards from Virginia Beach, ...

Promt before changing a value
I was asked the below question by someone and was wondering if you guys have any advise I can relay to him. "I am looking to create something, most likely with VB code, in excel that will pop up a dialog box to warn the user that they are changing the quantity of a part by a more than normal amount. For example, if the current inventory quantity for a specific insert is 10,000, then if that cell is changed by more than 3000, a dialog box will appear to prompt the user to double check their change. So, if that cell is changed to 13,000 or higher, or, 7,000 or lower, the dialog box will ...

Stop entering data if a specific field is Null
Hi I have a table where there is one field named "Apartment" I have put required Yes for this field. Here is the problem and I am surelly not the first to ask that. Right now, user can fill all fields and when he wants to add a new record, system tells that the "Apartment" field must be entered unless the record will not be saved. To add a new record, user have to click a button. When he clicks, focus goes on the "Apartment" field. What I want is if user don't fill the "Apartment" field he will not be able to go to other fields...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...