Storing A Calculated Field On A Table

Aug 10, 2007

Hi,

I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.

Gary

View Replies


ADVERTISEMENT

Storing Calculated Fields In A Table

Apr 20, 2007

I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.

I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.

For example:

Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).

So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.

Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?

Thanx for any help!

View 2 Replies View Related

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

General :: Storing Calculated Fields In A Table

Jul 26, 2012

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.

Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.

I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:

=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]

(Later I will add in all the iif's for if there is no preferred name)

Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.

I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.

View 2 Replies View Related

Storing A "Calculated" Field.

Oct 7, 2005

Hey there,

I want to store a "calculated" value from a form text-box. By calculated, i simply mean that it is a textbox that contains the following Data:
=[MemberFirstName] & [MemberLastName]
Basically, the field is the Members Username, the text box is called "Username" and it needs to be stored in "tblMemberInfo" under "MemberLogin" so that it can be used externally for the website.
The value is only going to be calculated automatically by the form once, and then it wont need to be changed again, which is why i want to store it in the db.

Any ideas on how i'd go about doing this?

Cheers
Fatmcgav

View 2 Replies View Related

Storing Calculated Fields

Aug 9, 2005

New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.

View 3 Replies View Related

Storing A Calculated Control.??

Jan 3, 2006

if i have formula in a text box can that calculation be stored in a table???

example

sales total

sales tax

grand total

where grand total = sales tax+sales total

if the control is bounded to a field in a table once the formula is entered doesnt it loose the connection with the field???

right now when i enter a formula in a control it will no longer store it in the table but it will only show the result in the actual form..???

View 4 Replies View Related

The Dread Of Storing A Calculated Value

Nov 5, 2004

Hi all, another beginner question here.

I have a table of records which is is basically temp storage, once I'm done inputing information, an append SQL statement is run to move the data to its permament table, then I use a delete statement to remove the values in the temp table.

Now, I have a column called Result, which stores the values of a radio button group (1, 2, or 3). I have a series of queries which count the number of times 1 or 2 appear (3 in this case, doesn't matter since it represents N/A. 1 = True, 2 = False, 0 = Null).

I'm having trouble getting the result of said query to store in a table. The statement is as follows:

SELECT [qryGetFail]![CountFail]+[qryGetPass]![CountPass] AS Total

FROM qryGetFail, qryGetPass;

This returns the sum of both counts, so I know how many questions were answered either Pass or Fail, hence I know how to score record. Here is my Append SQL statement as it currently works:

INSERT INTO tblAuditResults ( Question, Result, Comment, ACAPS, AuditDate, TeamMember, Auditor )
SELECT tblQuestions.Question, tblQuestions.Result, tblQuestions.Comment, [Forms]![frmAuditCreate]![txtACAPS] AS Expr1, [Forms]![frmAuditCreate]![txtAuditDate] AS Expr2, [Forms]![frmAuditCreate]![cmbTeamMember] AS Expr3, [Forms]![frmAuditCreate]![txtAuditor] AS Expr4

FROM tblQuestions

WHERE (((tblQuestions.Product)="4" Or (tblQuestions.Product)=[Forms]![frmAuditSelect]![txtVarProduct]) AND ((tblQuestions.Function)="3" Or (tblQuestions.Function)=[Forms]![frmAuditSelect]![txtVarFunction]));

This stores everything I need it to. But I cannot for the life of me get the statement to accept a value from a qeury and store it. I have tried adding another expression but it always just prompts for the value instead of pulling the value from the qryGetCalc.

Is there a specific way to have an INSERT statement pull a value off an active query?

View 3 Replies View Related

Sum Queries And Storing Calculated Fields

Aug 26, 2005

Hello All,

Probably trying to do the impossible.

Normally I would never (that’s a long time) have the results of a calculated field in a table, but I’m stumped.

Table1
TestID: Autonumber
Quantity: Number
Selected: Yes/No

Table2
SumID: Autonumber
TestID: Number, Foreign Key
Quantity Delivered: Number

Query1-Sum Query
Table2
TestID: Group
Quantity Delivered: Sum

Query2- Select Query
Record Sets

Query1 Table1
TestID-----------------------------------TestID
Quantity Deliverd Quantity
Select

Fields in Query2
TestID from Table1
Select from Table 1

Run Query
Can’t update “Select” field


So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.

View 2 Replies View Related

Tables :: Storing Calculated Values?

Mar 7, 2014

I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.

attached is my table and Input Screen.

View 3 Replies View Related

General :: Storing Calculated Fields

Sep 16, 2013

I have a score marking template for an internal audit. A criteria is either met, or it is not. All of these questions have Yes/No data type. each question has a weighting. I have written a VBA function to work out the total score by using a bunch of If statements.I need to store the final result in a table, by entering the answers (yes or no) into a form.

View 4 Replies View Related

Storing Calculated Fields - Example Post For SBaxter

Aug 15, 2004

Here is an example of what I was talking about re: private message and storing calculated fields. Any help is much appreciated.

Also I received your email in regards to a sample secure database but I am getting a "Memory could not be read" error when I try to open it. Does this have anything to do with how it has been converted to 97 format or is it an error at my end? (I've been able to open other dbases from the forum).

Thanks,

Dearnne

PS Thanks also for your help with adding to option groups.

View 4 Replies View Related

Forms :: Storing Calculated Control Of A Subform

Jul 5, 2015

Eventhough i know calculated control are for queries, I still need to store a calculated value of a sum. I have a form with customers, dates, locations. That form has a subform that contains the services done and materials used. This subform is in datasheet view. In the footer i have a control with =Sum([Price]).

So I need the value of this control to be stored in the table the main form is based on. I did an unbound control in the main form where the =Me!Subform.Form!.Textx will bring the value "over" Then for the on focus event the vba code Me.Total=Me.Textx stores the value. That is fine. The same works with a command button with the same vba code.

However i am asking if there is a more automatic way to get the value over without the unbound control/command button.

View 7 Replies View Related

Forms :: Accounting Program - Storing Calculated Value

Aug 5, 2013

I am trying to create an accounting program on access and i got stuck on the final touches

I'll explain my situation in brief. I created two tables

/1 for the invoices (invoice number, client name, total)
/1 for the orders (order id, invoice number, subtotal)

And I made a relationship between those two and everything worked out well. I created a form/subform and put a text box in the subform to calculate the sum which worked correctly. And I exported the value of the sum to the main form and it's great.

The problem is that how can i make the Total field that belongs to the table invoice have a default value as same as the Expression (sum) so that the sum is stored with the invoice record.

i attached the picture in the attachments

View 10 Replies View Related

Problem Storing A Value Calculated By Unbound Text Boxes

Mar 6, 2006

I have 3 values in unbound text boxes and a 4th text box that is calculating the sum of the first three text boxes, i need the result of the calculation to be stored in the field "Total Cost" that is also on this form.

Any ideas

Cheers Mikk

View 5 Replies View Related

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

Tables :: Auto-populate Table Field From Calculated Field In Another Table?

Jan 15, 2015

I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)

Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.

View 2 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Calculated Field In A Table?

Nov 12, 2004

Hi folks,
Is there a way to have a calculated field within a table? I have a table that I've added four fields to: AmountIT, AmountRE, AmountKK, AmountRes. I want to be able to sum these four fields to give AmountTotal, but is there a way to have in a table?

View 8 Replies View Related

Help Please Calculated Field In Table Or Other?

May 23, 2005

Im not sure the best way to go about this and would be most grateful if I could be pointed in the right direction.

If I had a table called tblEmployee and a field within it called "PayRate" and another table called tblHours with a field in it called "LabourCost" and a field called "Hours". What is the best way to hold the "LabourCost" value so it doesnt get affected if someones "Payrate" is changed.

I expect I have asked this all incorrectly and I really do apologise.

Many thanks in anticipation... :)

View 4 Replies View Related

Is It Possible To Have A Calculated Field In A Table?

Mar 31, 2006

In an orders table, i want to have a total value calculated... in the table... i was only able to do that in a query... but is it possible to do so in a table?

View 6 Replies View Related

Add Calculated Field To Table

Oct 12, 2015

I am calculating a field fldD. I have added the following to the Control Source of fldD "=[fldA]+[fldB]+[fldC]"

Can I save the value that is created back to the table i.e. fldD will be the value of =[fldA]+[fldB]+[fldC]

View 1 Replies View Related

Table Calculated Field Creation?

Feb 12, 2008

Is it possible (and logical) for me to create a table with a calculated field from 2 other fields in the same table? Example; I have a long time field (mm/dd/yyyy hh:mm:ss) aka StartTime, and a long time field EndTime. I would like to subtract the StartTime field from the EndTime field to get the total time elapsed, but in hh:mm:ss format. I can get it in hh:mm format, but I really need the hh:mm:ss format. Any Ideas? :cool:

View 4 Replies View Related

Calculated Field From Form To Table

Sep 18, 2006

Hello,

I have been reading various threads about this subject and I know that one shouldn't store a calculated field in a table.

I believe this may be an exception to the rule (but I could be wrong).
I am making a pricing database that will use a few manual inputs and the rest of the fields will be calculations (gross/profit margin, expenses) based off these inputs. We need to have these calcuated fields stored because they are essential to the pricing decisions and we will have to go back and access these fields at a later time. Another reason for this is because there will be at least one report that will be used to compare prices and calculated fields associated to a certain product by company, program, item type, ect...


Any help would be greatly appreciated.

View 2 Replies View Related

Tables :: Get Rid Of Calculated Field In A Table?

Sep 13, 2013

I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.

So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.

However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved