INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Should calculated table fields ever be used - if not - why not?

Should calculated table fields ever be used - if not - why not?

(OP)
I am in the process of developing at property database.

In the course of building various forms, I find myself having to build the same calculated fields in various queries time and time again. This becomes onerous and of course difficult to change throughout the database if necessary.

For example I will use the client name on numerous different forms using different queries and will have to concatenate something like the string below each time....

ie. [ClientTitle] & " " & [ClientFirstName] & " " & [ClientSurname] (The actual concatenation is much longer, but you will get the idea).

Access 2013 gives the option of using a calculated field at table level. Initially this looked great, in that I could build the calculated strings once at table level and then refer to them from anywhere in the database. Great for ease of use and future maintenance if necessary.

After researching a little more however, it seems that using calculated fields at table level is frowned upon by developers and it also suggested that should the expression change, then some records may not be updated correctly (although I can not replicate this).

I would welcome input as to the pro's and cons of using calculated fields and any real world experiences.

I understand that SQL server also has a calculated field at table level and so I'm surprised that its considered by some as a no go feature.

I am hoping to eventually move the back end to SQL server a year or so down the line if this has implications?

Many thanks as always - Mark


RE: Should calculated table fields ever be used - if not - why not?

Mark,
As I have stated before, I don't use calculated fields however I don't think I have heard of solid evidence when they aren't updated as other fields in the same record are updated.

I don't believe the calculated fields can be automatically upsized to SQL Server. You might need to rebuild them in SQL Server. There are also some simple functions that aren't available when creating calculated fields.

Have you considered making a simple query with all the fields from the table as well as columns calculated in the query for your various name formats?

Duane
Hook'D on Access
MS Access MVP

RE: Should calculated table fields ever be used - if not - why not?

(OP)
Thanks again for your input.

I would agree that I have never been able to
produce any errors in the calculated table field
results, although numerous comments seem to exist on
this matter from a Google search. Perhaps it's something
of a myth that is picked up and repeated.

I saw the info here.

http://allenbrowne.com/casu-14.html

Oddly I had not thought of a simple query based on the
whole table with the calculated fields added. It
seems a pretty straightforward solution!

So basically after I create this query, I could then use that query
much as I would a table, in so much as I could use it in building other queries?

Hopefully I have understood your suggestion. Many thanks Mark.

RE: Should calculated table fields ever be used - if not - why not?

>> in so much as I could use it in building other queries?

Yes, you can use a query in a query, when in 'Query Design' the 'Show Tables' GUI has a tab 'Queries'

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close