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

[Moss100]

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

 

[dhookom]

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

 

[Moss100]

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.

 

[1DMF]

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