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 Replies


ADVERTISEMENT

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 13 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

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

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

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

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

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

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

Storing Elapsed Time In A Field

Oct 8, 2011

I am currently in the process of creating an Employee Database. I have a 'staff information' table with several fields, one of which is a 'contracted hours field'. I have a spreadsheet linked to this database and therefore require this field to be formatted correctly in hours and minutes. For example...I wish to store a decimal time of 37.5 as 37:30.

View 1 Replies View Related

Field Storing/showing Minutes And Seconds Only?

Jan 5, 2005

ok.. i am storing CD info in a database, including track lengths, these are obviously in minutes and seconds.. I cant use short time because that is hours and minutes, which means any track longer than 24 minutes doesnt work.. i cant simply use text with an input mask because i want to be able to calculate the total length of the CD by adding the track lengths together.. if i use Long Time it also shows hours, which will confuse the user, and create more scope for errors.. any suggestions? I hope I am making sense here.. thanks for any suggestions :D

View 2 Replies View Related

Storing Multiple Dates In A Single Field

Feb 14, 2012

I am facing an issue with an access table.I have a field named "Contact Dates" , supposedly that field will store in a drop down list all the dates that the contact with the related company was made, how can I store it ? I am using in the current moment a memo data type to store the data and a comma for splitting them. How to create a drop down list for storing and displaying all the recorded dates and recording all the new data values as date format.

View 3 Replies View Related

Basing A Calculated Field On Another Calculated Field

Apr 12, 2006

My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.

I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.

Does anyone know how to correct this? Thanks in advance.

:confused:

View 1 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

Query Criteria - Text Field That Is Storing Both Dates And N/A

Jun 5, 2015

I have text field that is storing both dates, and "N/A."

I'm trying to to get only records that match this format "mm/dd/yyyy" or "xx/xx/xxxx"

Are there any format functions for query criteria, like must match this format "__/__/____" ?

I know I can just use "<> "N/A"" however I want "<> "N/A" AND Format = "__/__/____"

View 3 Replies View Related

Tables :: Field Value Got Truncated - Data Type For Storing Long Text?

Mar 10, 2014

I have encountered an issue when I was inserting a string (with newlines about 176 characters) into access table. This field in access has the data type TEXT and it was truncated after the insertion. It is strange because I have three other fields with the same format and no truncation at all. May I know what could be the reason and how/what is the recommend data type for storing long text?

View 3 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

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Tables :: Calculated Field Error - Cannot Define Field More Than Once

Jun 10, 2015

I use access 2010.

I am assuming by the error code, one can not use a calculated field to calculate another field.

View 1 Replies View Related

Calculated Field

Feb 9, 2006

Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]

There are 2 tables:
tblPartsList
PartNum
PartDesc

tblPartsListCategories
OrderPrefix
OrderNumCategory

This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.

How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*

Having been trying to do this for ages but having trouble with syntax.

View 10 Replies View Related







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