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..???
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.
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 RelatedHi 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?
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!
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
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.
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.
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 RelatedHere 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.
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 RelatedI 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.
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
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
Hi guys,I'm new to Access except using it years back but much has changed from the 2000 version to the 2007 version - it's slowly coming back to me though, as it's mostly just an interface change. Although I'm highly experienced in SQL (mySQL) and database design / structure from being a Web Programmer.I'm wanting to store a stock amount for products. Using 3 tables, for products, orders and sales. Here's a real basic layout:productsprodID | productName | productStockordersorderID | supplier | prodID | quantityBoughtsalessaleID | customer | prodID | quantitySoldThe products table is just a list of the products. Sales is where I log what I sell to customers and the orders are what I buy from my suppliers.What I'm wishing to do is use the products table to store the stock of that item so as soon as I make a sale, that quantity for that prodID is subtracted instantly from the productStock and when I make an order it's instantly added.Is this possible, or is there a good way to do this?All help appreciated! :)Thanks,Nick.
View 2 Replies View RelatedHey 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
Here is the SQL that A2K gives me for the query that has the actions I want it to perform. But I can't get it to give me a count of the number of records returned. I would love to use DCOUNT but I am having a tough time working it out. Also in the final soultion, the numbers 2 and 23 are fields that I have 2 = weekday and 23 = [weekday] + [tt] which are in a different table. If someone could help me out with the DCOUNT formula, I would be forever appreciative.
SELECT DISTINCTROW noweekend.noweekday, noweekend.dayid
FROM noweekend
GROUP BY noweekend.noweekday, noweekend.dayid
HAVING (((noweekend.noweekday)=1) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23)) OR (((noweekend.noweekday)=7) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23));
Hi,
I am trying to create a calculated field in a query which uses another value in the query to lookup a table. I get an error running the query :
"Microsoft Access can't find the name 'Indcat' you entered in the expression."
The expression I am using is as follows:
Expr1: DLookUp("[Class]","Class","([Indcat] = [Clind]) AND ([2007] Between [From] AND [To])")
Indcat and 2007 are valid field names in the query.
I used this Dlookup in a form and it worked fine (with Form! etc references to Indcat and 2007)
Mystified
Tony Randell
Is there a way to get a calculated Control of a form to end up in the underlying table?
View 2 Replies View RelatedHi,
I am a first time user to access groups, so I am hoping for a
good experience.
I have a form which has a calculated control. But the calculation
shows on the form but is not storing in the record.
On the property sheet, the name is charge and the control source is
the calculation (=[source1]+[source2]...)
Why is it displaying but not storing? I do not know Visual Basic, so I would
like a solution that is simple if there is such a thing.
Thanks in advance,
Trainjoan
Is it possible to use an input mask on a calculated control? I have a field which will return "Home Phone:" [HomePhone] and I'd like it to actually display the phone number as (000) 000-0000. Is there a way to do this? Thank you!
View 1 Replies View RelatedI have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
I have a report and in the footer I have added a text box (Textbox136) to work out the average percentage of the field [Percentage], which works fine.
For the value in textbox136 I want to output a grade and want to use an IIF function in the control source, something like:
IIf([Textbox136]>=100,"A+",IIf([Textbox136]>=90,"A+",IIf([Textbox136]>=80,"A",IIf([Textbox136]>=70,"B",
IIf([Textbox136]>=60,"C",IIf([Textbox136]>=50,"D",IIf([Textbox136]>=40,"E",IIf([Textbox136]>=30,"E-","U"))))))))
But it won't work.
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
I am trying to use DLOOKUp and Trim in a calculated control on a form - D look up works but when i add Left trim to trim a telephone number to just the prefix - it wont work
=DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form]![Data] & "'") - Works OK
=DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form],7) & "'") - appears to put square brackets around Left ??
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
View 2 Replies View Related