 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007Calculated field in a Form
I have a calculated "Total" field in my form. It calculates just fine but, the value will not populate to the table. I open the table, see the field but it remains BLANK. Need assistance, s.v.p  SignatureBrigitte There is no point on saving the value of a calculated field, you can always achieve that using a query In a query: NewFieldName: [Field1]+[Field2] Saving a calculated field, mean that you'll have to maintain it, and it can cause mistakes. e.g: What will happen if someone will update one of the values directly in the table? the caculated field will be wrong. A query will always give the right and most current resault If you need help on how to use the query to calculate the field, please explain what you need to do. SignatureGood Luck BS"D > I have a calculated "Total" field in my form. It calculates just fine but, > the value will not populate to the table. I open the table, see the field but > it remains BLANK. > Need assistance, s.v.p Why does everyone keep saying that there is no reason to save the result of a calculated field? I will need the results of my calculated fields next year when I do my 1st quarter reports. I will have to compare sales with this year's 1st quarter. The calculations are done in a query. But I need to save the results somehow, before I can enter the data for the 2nd quarter. Right now I am creating a field in the table "1stqtr2007" and manually entering the results of the calculated field into this field. There are over 200 customers for some of the lines we sell. That's a lot of mundane data entry. I have to be able to recreate quarterly reports at my boss' whim, and also I will need this information next year. Otherwise I would be re-entering all of the data from this year (work already done) in order to have a comparison to next year's 1st quarter data. There has to be a way to save these values without manually entering them into another field. I don't want them to change every time new data is entered into the year to date field. I absolutely need that info to remain static. Help! > There is no point on saving the value of a calculated field, you can always > achieve that using a query [quoted text clipped - 15 lines] > > it remains BLANK. > > Need assistance, s.v.p these kids just don't understand how to use calculated fields they're scared of it because they think that it introduces complexity-- but they are wrong by all means store calculated values if it makes your life easier or your reports faster > Why does everyone keep saying that there is no reason to save the result > of a [quoted text clipped - 45 lines] >> > it remains BLANK. >> > Need assistance, s.v.p Ignore this person. He has become oh-so-clever and started using a variety of names, but the message source is the same as for a number of other postings from a person with a demonstrated ignorance of Access and too much time on his hands. > these kids just don't understand how to use calculated fields > [quoted text clipped - 55 lines] >>> > it remains BLANK. >>> > Need assistance, s.v.p They keep saying it because it's so. You are thinking in spreadsheet terms. A new field for each quarter is not correct design. It also greatly limits your flexibility. If you create a query based on a table, you can use something like this for the criteria for a date field (I will call it DateField): Year([DateField])=[Enter year] And DatePart("q",[DateField])=[Enter quarter] you will be prompted for a year and a quarter. The quarter will be literal (1st quarter is Jan, Feb, March), but sometimes the start and end of a quarter are taken to be a Monday and a Sunday, or something like that, so that there may be some April dates in the first quarter. You can adjust to that. You can also specify a date range, or look at the first quarter for all years, or whatever you like. A crosstab query can provide a quarter by quarter sum, or you can group reports by quarter with the quarter's total included, or you can mix and match the above. You can do lots of other things, too, with a proper design. Your database's design may be holding you back. Without knowing something about the database's structure and purpose it is difficult to offer specific suggestions, but keep in mine that a table contains information about a single real-world entity. Customer information should be stored in its own table. Transactions with that customer are stored in a separate, related table. A transaction is not an attribute of a customer, but rather one of many such events that are related to the customer. Take a look at this site, particularly the Design section of "Tips for Casual Users". http://allenbrowne.com/tips.html There are links on the right side of the page that can guide you to more information and explanations. > Why does everyone keep saying that there is no reason to save the result > of a [quoted text clipped - 45 lines] >> > it remains BLANK. >> > Need assistance, s.v.p I should add that there are rare legitimate reasons for storing a calculated field, but I doubt you have that situation. > Why does everyone keep saying that there is no reason to save the result > of a [quoted text clipped - 45 lines] >> > it remains BLANK. >> > Need assistance, s.v.p >I have a calculated "Total" field in my form. It calculates just fine but, >the value will not populate to the table. I open the table, see the field but >it remains BLANK. >Need assistance, s.v.p It is blank, and that's the way it should be. The Total field should simply NOT EXIST in the table. 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] >>I have a calculated "Total" field in my form. It calculates just fine but, >>the value will not populate to the table. I open the table, see the field [quoted text clipped - 19 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.