You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
When I talk to developers about why they love Access, the most frequent response is always about fast they can build applications that track data. This saves time and money. Calculated columns are a new feature in Access 2010 that is designed to help end users and developers build applications faster, and are easier to maintain.
Let me start with a couple scenarios from the Access 2007 templates. In the Contacts database, there are two fairly complicated expressions that conditionally show either the name of the person or the company, depending on which is blank:
FileAs: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName]))
ContactName: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]))
The Sales Pipeline database has a similar scenario with a different expression that calculates the value of the potential opportunity:
ForecastValue: [EstRevenue]*[Probability]
In these two scenarios it is highly likely that if you are going to use data from either of the tables, you will also need the calculated fields.
In the templates, we created a base query that did a SELECT * and included the expression columns. Unfortunately, I see many poorly designed databases in which an expression is repeated throughout queries, forms and reports. When the expression requires modification, the database owner must look for the many places it may have been used so all the occurrences can be changed. With calculated fields, there is only one place to edit, so maintenance is faster and easier.
The main benefit of calculated columns is encapsulation, which leads to cleaner database design. It allows database developers to define the most common fields at the table level. This makes applications easier to maintain as inevitable bugs creep up in expressions (at least if you write expressions like I do :-) ). The developer can easily track the expression back to one place—the table.
Note: data macros are another example of a feature that allows developers to encapsulate logic in the table rather than behind form events.
Calculated columns are easy to create. Open the table in browse view. From the ribbon, select Table Tools | Fields | Add & Delete | More Fields | Calculated Field or choose the Calculated field type in table design view. Either way, the expression builder will help create the expression.
Another benefit calculated columns provide is consistency with SharePoint. Microsoft has made a big investment to make SharePoint a platform for collaboration and data applications. Calculated columns were introduced in SharePoint 2003 as a “calc and store” model, where values are only recalculated when dependencies change. This model is a performance optimization that makes it faster for the server to query against these values, especially when users sort and filter on the calculated field.
The calc and store model cannot be used for volatile expressions such as Date() and Now() because the stored result would only be accurate at the moment it is initially calculated. Functions that reference outside the row of the table, such as DSum, DCount and DLookup are also prohibited because it would be costly from a performance perspective to detect when dependencies have changed. These types of calculated fields are better left in base queries.
Calculated fields can save development time, reduce errors, and increase performance. This results in better-designed applications.
Enjoy!
Comments: (10) Collapse
I am perennially surprised to see such unclever use of Access in the sample files Microsoft provides. A cleverer way to calculate the FileAs and ContactName fields could to use the Mid() function and capitalize on propagation of Nulls. Thus: Mid(("12"+LastName) & (", "+FirstName) & (": "+Company), 3) If the name fields are Null, you'll get the company name. If the names fields are not Null and the Company name is Null, you'll get just the name. If both name and company are filled out, you'll get the name followed by the company name. Now, that's not exactly the same result as the above, but it's probably just as useful at least for the FileAs field. While this doesn't change anything about the utility of calculated fields, it does make for a much easier concatenation, with one function call instead of three.
thanks, Clint. Calculated Fields look like they will be very useful, especially for names! ~~~ nice equation, David -- using Mid is a clever idea ~~~ I would like to know what common equations others use -- here are some of mine: ********************************************************** ~~~~~~~~~~~ FileAs Name1 & (' '+Sufx) & (', '+Name2) & (' '+MidNm) & (', ' + Title) --> Phillips III, Joseph P., Dr. --> Taylor, Elroy --> ABC Tires ~~~~~~~~~~~ Greeting_Formal (Title + ' ') & IIF(Not IsNull(Name1), Name1, Name2) --> Dr. Phillips --> Mr. Flintstone --> ABC Tires note: in code, you can do this: (Title + ' ') & nz(Name1, Name2)
but NZ is not allowed in a calculated expression for a field definition ~~~~~~~~~~~ Greeting_Informal IIF(Not IsNull(Name2), Name2, (Title + ' ') & Name1) --> Dr. Phillips --> Fred --> ABC Tires ~~~~~~~~~~~ FullName ([Title]+' ') & ([Name2]+' ') & ([MidNm]+' ') & [Name1] & (' '+[Sufx])+("("+[NickNm]+")") --> Dr. Joseph P. Phillips III (Joe) --> ABC Tires ~~~~~~~~~~~ CityStateZip ([city]+', ') & [st] & ' ' & [zip] & ('-'+[ZipExt]) --> Denver, CO 80201 --> Chicago, IL 60601-1234 ~~~~~~~~~~~ CityStateZipCtry ([city]+', ') & [st] & ' ' & [zip] & ('-'+[ZipExt]) & (' " + [Ctry]) --> Denver, CO 80201 --> Chicago, IL 60601-1234 US --> Amsterdam NL ~~~~~~~~~~~ Birthday IIf(IsNull([DobYr]) Or IsNull([DobMo]) Or IsNull([DobDa]),Null,DateSerial([DOByr],[DOBmo],[DOBda])) since I store birthday as 3 fields (in case year or day is not known), this will be handy to make a value for DOB **********************************************************
WHERE
- Name1 is the main name -- last name for human or company name.
- Name2 is the secondary name -- first name for human or for companies, if applicable, subsidiary or division ********************************************************** Another equation I use is stripping x characters from the beginning of a memo field for sorting. For long text fields, I often sort by a stripped version of the field as well. This cannot be done with a calculated field if it involves a memo data type, but it can be done with a data macro. Warm Regards,
Crystal * (: have an awesome day :) *
Very nice - often create calculated columns in queries but I like this idea of creating it once in the table and having it available throughout.
I don't see much value in Calculated Fields at the table level. Why not just create the Calculated Field in a query and then just make sure that you use this query for any related forms, reports, or other queries? Is there a speed (or other) benefit to having this in the table? Is this really encapsulation... or feature bloat? :)
Does SQL Server support calculated columns also or will these not be upsizable?
>Why not just create the Calculated Field in a query That is a great suggestion/question. I should point out that I never met an designer who could create a query and then for every single use in the application now NEVER build another query on the table and always build an query on top of an query. If you add columns to the table then do you go modify that query to display the additional collum? I suppose in an perfect world every query and every bit of code could be based on that ONE query. I found in the real world, we generally don't see this. The other thing that comes up here is access now has a new octopus arm growing out it side that allows one to build web applications. So perhaps your query is not even published to the web site. Now what? At the end of the day, this just gives an Full Name column that is ready made for reports, for forms, for triggers, for VBA code, for web forms etc. I mean, should my triggers or VBA code always be forced to use that one query because it has some expression? And, perhaps your SmartPhone or some web service is hitting the web site and you not even using sql anymore. We live in a very connected world, so just the desktop, or just the web site, or just the SmartPhone are only an small sample of the brave new future in store for us. So, often many of these new technologies will not even be using sql anymore. Having an handy dandy place for this expression that is outside of the code or sql and can be consumed by any conceivable and even unimagined technologies seems quite an useful feature to me. If you open up the table in with c++ or vb.net, you will have that expression instantly available as an plane jane column. You don't have to worry or even know about some sql statement you supposed to use. > Does SQL Server support calculated columns also or will these not be upsizable? Yes, sql 2005, and 2008 support table expressions. As to if the upsizing wizard would convert these expressions is another matter, but in theory it should be possible. Albert D. Kallal (Access MVP) Edmonton, Alberta Canada kallal@msn.com
Hi! I need a formula to tell to the "Task Detail" window that if I chosed from the rip menu "Name" using formula shown up I´d like to make Access to fill by itself another menu "Department" as every Name has department also in Contract table. Hope that you unerstanded me and can help.
Albert wrote "I should point out that I never met an designer who could create a query and then for every single use in the application now NEVER build another query on the table and always build an query on top of an query." However, if we want to keep some logic centralised without using calculated fields in Access 2010 this might be what we would do. Prior to Access 2007, we have to do this. I've yet to be fully convinced by calculated fields in Access tables (partly because I haven't looked at them much yet!). Some months ago I came across an article at warin.developpez.com/.../nouveautes which caused me to keep a mental note to do some testing first if I ever thought about using calculated fields in a table though. The author of this article (which is in French) came across some problems. It his tests he found that with a database containing only one table, the size of the database grew markedly when there was a calculated field in that table as opposed to the same table but with a field where an external process, e.g. an action query, had inserted the calculated value so it was stored as a normal field. The size increased from 912KB to 1284KB. This was after repeated compaction. He also found that insertions of records took longer, i.e. insertion of 10,000 records took 13.06 seconds instead of 9.04 seconds on average. Though the use of a calculated field may well be the way to go in some situations, it looks like a developer would be advised to look at other possibilities as well, e.g. basing queries on other queries, which, of course, may well have their own problems with speed or complexity.
Woops, make that "Prior to Access 2010, we have to do this."
"It his tests he found that with a database containing only one table, the size of the database grew markedly when there was a calculated field in that table as opposed to the same table but with a field where an external process, e.g. an action query, had inserted the calculated value so it was stored as a normal field. The size increased from 912KB to 1284KB. This was after repeated compaction. He also found that insertions of records took longer, i.e. insertion of 10,000 records took 13.06 seconds instead of 9.04 seconds on average." None of this is surprising. After all, third normal form was created to avoid both these issues. However, like any other justifiable violation of third normal form, I think it's important to understand when it's best and when it's not recommended to use calculated columns in Access.
Comments: (loading) Collapse