How do I store calculations from a form to a database table?

My boss and I are stuck trying to figure out how to make our calculations be 
stored in our database table. Currently our calculations are in the design 
view of the form. We are able to see the anwer to the calculations, but they 
are not stored in the database table. What are we doing incorrectly? Is there 
some other place or way that we are supposed to do calculations?

Thank you for your help.
0
Utf
2/16/2008 12:09:00 AM
access 16762 articles. 3 followers. Follow

5 Replies
1278 Views

Similar Articles

[PageSpeed] 0

The short answer is - don't. In most cases you should not try to store 
calculated values in a table. Calculations should be done in queries 
or in unbound controls on forms. Storing the value in a table is 
rarely necessary.

-- 
_________

Sean Bailey


"schindy" wrote:

> My boss and I are stuck trying to figure out how to make our calculations be 
> stored in our database table. Currently our calculations are in the design 
> view of the form. We are able to see the anwer to the calculations, but they 
> are not stored in the database table. What are we doing incorrectly? Is there 
> some other place or way that we are supposed to do calculations?
> 
> Thank you for your help.
0
Utf
2/16/2008 12:16:00 AM
"schindy" <schindy@discussions.microsoft.com> wrote in message 
news:9F6FC793-E62A-4AAE-B5C2-9B60108E2DEB@microsoft.com...
> My boss and I are stuck trying to figure out how to make our calculations be
> stored in our database table. Currently our calculations are in the design
> view of the form. We are able to see the anwer to the calculations, but they
> are not stored in the database table. What are we doing incorrectly? Is there
> some other place or way that we are supposed to do calculations?
>
> Thank you for your help.

What you are doing incorrectly is WANTING to store them.  Calculated values 
should be calculated as needed, not stored.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
2/16/2008 12:18:34 AM
On Fri, 15 Feb 2008 16:09:00 -0800, schindy
<schindy@discussions.microsoft.com> wrote:

>My boss and I are stuck trying to figure out how to make our calculations be 
>stored in our database table. Currently our calculations are in the design 
>view of the form. We are able to see the anwer to the calculations, but they 
>are not stored in the database table. What are we doing incorrectly? Is there 
>some other place or way that we are supposed to do calculations?
>
>Thank you for your help.

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 just as you're now doing it -
in the control source of a Form or a Report textbox.
-- 
             John W. Vinson [MVP]
0
John
2/16/2008 6:12:46 AM
What the others have said is true, except in situations where the calculation 
is on values which might change over time, but you want the results to 
reflect the values at the time the calculation was first made.  An example is 
an invoice, where you might base the invoice amounts on the unit price of a 
product multiplied by the quantity supplied.  The unit price would be in a 
products table, but would change over time.  If the invoice amounts were 
computed from the values in the Products table then old invoices would 
reflect the current prices, whereas they should reflect the prices at the 
time the invoice was raised.

In these circumstances you should either assign the unit price value to a 
field in an InvoiceDetails table and compute the gross price on that value in 
the form's underlying query or an unbound control on the form, or compute the 
gross price and assign that to a field in the InvoiceDetails table.  Each 
invoice will then respect the original prices.  You'll find an example in the 
OrderDetails sub form in the sample Northwind database, where the unit price 
is looked up and assigned to a field in the AfterUpdate event procedure of 
the ProductID control.

Ken Sheridan
Stafford, England

"schindy" wrote:

> My boss and I are stuck trying to figure out how to make our calculations be 
> stored in our database table. Currently our calculations are in the design 
> view of the form. We are able to see the anwer to the calculations, but they 
> are not stored in the database table. What are we doing incorrectly? Is there 
> some other place or way that we are supposed to do calculations?
> 
> Thank you for your help.

0
Utf
2/16/2008 6:49:01 PM
Thank you everyone for helping us. We are finally back on track.
0
Utf
2/19/2008 7:03:02 PM
Reply:

Similar Artilces:

Form fields vs. document properties
I'm creating common templates for multiple dealers of the same product. For example, one dealer may open my "Client Guide" document and all he needs to do is update approximately 20 fields and he is done. I'm using the standard document properties fields for my company information, but there are 10+ more unique fields I need to do this for. I've been trying all night to use quick parts, building blocks, etc. to accomplish this but I am really struggling. I can use a fill-in field, but it prompts for the field every place I use it. I've enabled th...

Calculation / Format Error
I'm using this formula in cell AE69 =IF(AL69="NO","",AE68*(AM69/100)) When the logic is true I want the cell value to be zero, but show as blank. When this cell is summed up with others, a #VALUE! is returned. I am assuming excel is seeing this cell as text and thus returning an error. I have tried adjusting the formula to: =IF(AL69="NO","0",AE68*(AM69/100)) I have selected the accounting formating for AE69 with the "£" symbol. However, instead of getting the £ symbol to the left and a dash representing z...

Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I run the macro the table gets created without a problem but when it goes to create the pivot table I get an error and the debugger highlights this section: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Table8", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _ :="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _ xlPivotTableVersion12 Can anyone see where the problem is happening? My ...

Sub Form
I have a main form and a subform each has a table. The problem I'm running into is when I get to the subform, and try to enter info into it nothing happens. Also if I click in the subform it'll work but it starts at the last field in the subform. I need it to go to the first field on the subform. How would I get it to do this? I was told something about the label from the subform that something has to be entered into this. Thanks > when I get to the subform, and try to enter info into it nothing happens. What do you mean? Are you saying that you cannot even enter data in y...

require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but the machine I'm on uses Excel 2002, and most of the users are on Excel 2000. The first 2 questions on my form require that the user select from a list of names, and then select their relationship to that person. Then the users answer a series of questions about this person's job performance. I'm using data validation to manage the two drop-down lists - the names and the relationships. However, about a third of my users end up leaving one or both of these cells blank. I have a comment box set up as a remin...

Pivot Table % of
I have a pivot table like the followng: Joe Trans Ct A 5 B 3 C 2 Total Joe 10 Kelly A 6 B 1 C 4 Total Kelly 11 I added another Count of Trans shown as a "% of column", but it gives me the percent of the entire column when what I really want is the % of Total Joe, % of Total Kelly, etc. In this instance I simply made the names go across instead of down which solved the problem, but there are instances where I don't want to do that. Any suggestions for getting the percent per item rather than pe...

Pivot Table Updates Issue
My Data had a coloum containing stuff like Marketing Department Sales Department Shipping Department I built a bunch of pivot tables on this data. Then I changed the data to read: Marketing Dept Sales Dept Shipping Dept and performed a Refresh Data. Now, on the filter pulldowns on any of the Pivot tables, I see Marketing Department Sales Department Shipping Department Marketing Dept Sales Dept Shipping Dept The first three option no longer exist in any of the data, and I do not want them in the list. How to fix this problem without recreating and reformatting 38 seperate Pivot table...

Pivot table row header
Hi, In a pivot table, I have 2 fields in the row section. How can I have the value on the first fields to repeat in every row instead of just showing once? Thanks!! Try Debra Dalgleish's page at: http://www.contextures.com/xlDataEntry02.html Fill Blank Cells Fill Blank Cells Programmatically* [*Sub FillColBlanks() 'by Dave Peterson 2004-01-06] -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jason" <Jason@discussions.microsoft.com> wrote in message news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...

how to create escalation form
How do I create escalation form. Abe wrote: > How do I create escalation form. Since you're the only one here who knows what you're escalating, to whom, in what way and for what reason, you are the only one who can tell what the form should look like. In general, you should create a Word template: http://www.word.mvps.org/FAQs/Customization/CreateATemplatePart1.htm If the form needs to have text that doesn't change, and places to enter text that does change, then you should create it as a form template: http://www.word.mvps.org/FAQs/TblsFldsFms/LinesInForms.h...

Database Engine Tuning Advisor and autocomplete
Hello there, in the never ending quest for better GP performance, I am onto indexes. I am convinced that there are additional indexes that can improve GP in real life situations, but have been unable to find anyone listing indexes they have used. So my question is, has anyone used the tuning advisor in sql server 05 and implemented its reccomendations? Were there noticable effects on GP performance? While I can run a trace against production and the advisor against a dev environment, I am still wary of implementing the changes in production... On a side question, where are the actu...

Best Practise
Hi Everyone, In the process of creating a new Exchange 2003 SP2 Cluster, and had some questions about breaking up the stores. I am a bit confused between Storage Groups, and Mailbox Stores. To break things down, here is the current setup on the existing Exchange Server (the non cluster one) 400+ Users Store is at 110GB Backup Time - Long When I start moving staff over to the new Exchange box, we want to implement quotas, and break up some of the accounts. Thought process was this: 1)Directors\Management\Tech - No Quotas 2)Supervisors and other Critical Staff - 700MB 3)Other staff - ...

time calculation
I need to do a check on the time.. if it is after midnight but before 5am then I need to minus 1 day off of the date. How could I do this? Hi If the Date and Time is in cell A1, use =IF(AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")),A1+1,A1) ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** ...shortened versions would be =A1+AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")) ** Posted via: http://www.ozgrid...

create pivot table formula without the GETPIVOTDATA function
Previous to Excel 2003 I could use a pivot table result in a formula and copy that formula as a relative reference. In Excel 2003 a GETPIVOTDATA fromula is created and uses an absolute reference. Is there a way in Excel 2003 to either make it arelative reference (without editing the formula) or revert to the method used in all prior releases? There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html ebergkes wrote: > Previous to Excel 2003 I could use a pivot table resu...

Creating Forms
I want to create a form on my website but do not know which program to use. Should using html be adequate for the purpose? On 26 Mar 2007 10:16:47 -0700, stromo wrote: > I want to create a form on my website but do not know which program to > use. Should using html be adequate for the purpose? You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever Office program you are using. I would suggest you include your Windows and Office version number in the message...

Pass a command text in pivot table
I've got a pivot table with data give back by odbc (in excel 2003). When i pass a new sql command, it answer me the database, also if i pass the connection string. My code is this: With ActiveSheet.PivotTables(sNamePivot).PivotCache .CommandText = sSelect .Connection = "ODBC;DSN=DS;DB=" & NomeDB & ";SRVR=;UID=SYSADM;PWD=" .Refresh End With sNamePivot --> string variable contain the correct name of the pivot table. NomeDB --> string variable contain the database. sSelect --> variable contain the sql command. Why does it called me ...

Pivot Table: showing data as % of subtotal
Hi, I have pivot table data in this format: Name Type Amount ABC 1 $100 2 $200 3 $300 4 $400 ABC Total $1,000 DEF 1 $150 2 $260 3 $310 4 $420 DEF Total $1,140 and so on .... .... .... I want to see the data as follows: Name Type Amount % ABC...

Opening a blank form
Hi guys, I created a form to consult data, the record source is a query that gather information from different tables, the problem is when I open the form it shows data already, I want a blank form when it opens, I already tried including DoCmd.GoToRecord , , acNewRecord in the open from event, but I get “You can go to the specified record”, if I go to the query I can’t modify the data or add a new record that is why the acNewRecord is not working. I’ll really appreciate any help Thanks Maracay wrote: >I created a form to consult data, the record source is a query ...

ACCOUNTS RECEIVABLE / STORE ACCOUNT
For the past three weeks or so we have been implementing RMS and allowing charges to be made to Tendered "Store Account". Now when we go to print accounts receivable or statements, no data/balance is available. Purchase history shows that a charge was made to the "Store Account". Help... a statement is no good if it does not show a balance. (The company who sold us RMS was fired months ago do to gross incompetence.) What is the Tender Type setting of the "Store Account" Tender? SO Manager, Database/TenderTypes Glenn Adams Tiber Creek Consulting http://...

Program accessing address stored in outlook
Every time I try to open an email in Outlook 2002 I get a message saying that a program is trying to access e-mail addresses stored in Outlook... How do I make it stop? I have scanned for viruses and such, I'm clean. Please help. (PS - this seems to have started right after updating to the latest office service pack). Do you have third party programs installed for spam detection or antivirus filtering? Have you visited the vendors of these programs for the latest updates? Are you using Word for the message editor? Try turning off Word for the meantime until you visit your thi...

how to create referential integrity between two tables in diff dat
How can we create referential integrity relationship between tables in different databases. I have databasea db1 and db2. In db1->TableA,db2-->TableB TableA-->EmpID,EmpName TableB-->EmpID,DeptNo. How to create integrity relationship between Empid of two tables in diff databases. Hi Ajay You cannot create DRI between databases, I mean create PK/FK... However , you can simple join the tables between databases like SELECT <columns> FROM db1.tb1 T1 JOIN db2.tb1 T2 ON T1.col=T2.col WHERE.... "Ajay" <Ajay@discussions.microsoft.com> w...

Chart- View Labels- Shared Database...
I am working out of a database used by two other individuals. When I attempt to print a report, my chart does not display the labels. If I open the chart in design view, I am unable to view the labels, but if I go further and actually open the chart all information is displayed. The other individuals working in the database are able to readily review the reports and print the data without any problem. We are all using the Access version, and I have already installed the Office Service Pack 3 fix. Nothing seems to working. Any assistance is greatly appreciated. ...

Adding row to table in Publisher 2003?
I created a table in Publisher 2003 with two rows and four columns. I added four pictures to the 1st row ( one in each column), and added the text in the 2nd row (each text item to correspond with the picture above). Now I wanted to add a row above the first 1st because I wanted to add a title, then put a border around the table. But when I select Table, the first row, and select Insert Row ABOVE, the row always ends up BELOW the row I selected. What do I need to do? Thanks for help! Susan I cannot duplicate this. Are you highlighting the complete row? You need to only place your curs...

Form/ subform link problems
My parent form holds information about a part and my subform holds ordering information for that part. Up to now I have linked the child to the parent via PartID : RecordID and Component Name : Component name. This is great as it shows the component name on the subform after it has been found on the parent form. The only problem with this is if the user changes the component name it does not keep the order information for the same item with the old name. I want to link purely on the partID : RecordID so that whatever else is changed the order information history still shows. When I do t...

Percentages in Pivot table
i have got a query looking at Sales History against Forecast and want to see the accuracy to forecast as a percentage in a pivot table. The fields I have are ActualSales ForecastQty Var: IIf([ForecastQty] Is Null,[ActualSales],([ActualSales]-[ForecastQty])) Perc: [Var]/[ActualSales] This all works fine if I run the query but when I try and put it into a pivot Table it doesn't like it and crashes. Is there a different way to handle this in Pivot tables. Thank you Hi Is this not possible could somebody let me know please. Thanks "Nicky" wrote: > ...

Maintaining rows in a Pivot Table
I have an Income Statement that has account numbers and subtotals. I want to keep the hierarchy/order if possible but it seems when I put the data into a pivot table that it puts everything in numerical order followed by alphabetical. So is there a way for it not to sort my account data? Thanks, Steve You could create a custom list of your account numbers, and base the sort order on that. In a cell on a blank worksheet, type your list, then select it Choose Tools>Options Select the Custom Lists tab Click the Import button, click OK When you create a new pivot table, items should b...