Solved

Calculated Field in a table

Posted on 2015-01-23
13
118 Views
Last Modified: 2015-01-29
I have a calculated field in my table and it's not returning any results
0
Comment
Question by:Jass Saini
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 84
ID: 40566725
That's sort of like telling your auto mechanic "My car doesn't work" and then walking away.

Can you show the design of your table, or upload a copy of your database here? There are a few caveats with calculated fields: They cannot refer to columns in other tables, and they are read only.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40566733
A little more detail would be appreciated.

YOu cannot have a "calculated field" in your table, you can have one in a query, or you can have a field in your table based on a computation including one or more fields.

If either of the fields used in the computation includes NULL values, then the result of that computation will always be NULL.  Can you provide the SQL for the query?
0
 

Author Comment

by:Jass Saini
ID: 40566767
Hello Dale,

That was it..Some of the fields are blanks.  I tested it by placing a 1 into and I got a result for that record..How do I correct that to include Null fields as well
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40566815
You can use Nz() to convert nulls to zero.  For example, instead of:

[FieldX] + [FieldY]

use

Nz([FieldX], 0) + Nz([FieldY], 0)
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40566881
@Dale.
In Acc2013 (.aacdb format) you can have a calculated field in the table...
Calculated filedGranted I would still not use this, ...but fwiw, ...it is possible.
0
 

Author Comment

by:Jass Saini
ID: 40566896
Hello Jeff,

Why would you not use it??  I am trying to keep a running total for each record in my table.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40566932
@Jeff,

Great to know.  I haven't explored 2013 enough to be familiar with that "feature".  Seems to me that it is as misguided as the multi-value fields "feature".

I guess if this feature functions like a data macro or trigger that will automatically update that field when one of the associated values changes, it could be useful.

@Jass Saini,

Generally it is considered bad database practice to store a value in a field that can be computed based upon the values of other fields in the table (or database).  This is probably a throwback to the days when disk space was valuable, and you didn't want to waste it storing a value that could be computed at run-time.

The problem is that if that behavior is not standard behavior across all databases, then when porting that data or application over to another back-end database, you may have to totally rewrite your code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40566944
These types of "enhanced, Access only" datatypes always come with caveats.
One is that this type of field cannot be upsized if you move up to SQLserver.
And because it is new, ...It also causes confusion if you open table and view the contents, and not know if this is a "stored" value or not.
Also, a query is a lot better at doing this than a table would be.
Finally, fields like this always have things that complicate your design down the line

Note that this field will not really create a " running total"
A Report will be the easiest place to get a running total.

JeffCoachman
0
 
LVL 84
ID: 40566958
I believe the Calculated Field was introduced in 2010.

While I completely agree that storing Calculated Fields is bad, in this case it's essentially a "trigger", in that the Calculated Field is updated anytime the source fields are updated, so there's no risk in the lack of update. Still, as Dale has said, you should avoid these, since your data will not upsize properly, and you could have issues if you try to export these odd datatypes to Excel or Word.

That said - there is simply no reason to use a calculated field unless you're archiving data (i.e. storing data that will ONLY be reported on, and NEVER AGAIN updated). If you need to calculate something, do it on the fly as needed.
0
 

Author Comment

by:Jass Saini
ID: 40566960
Thanks..I Changed the field back to a "Number" field..as it was not resulting anything unless all the fields had a number in it....and I tired NZ([Field1,0) and it would not work...so Now I am trying in my query
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40566963
@Dale,
Yep, As I stated, I would still stay away from using this datatype.
To me these "Access only" fields, cause more confusion than they are worth.
...That and the fact that MS might think the same thing, and deprecate them in the future...
:-O

Jeff
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40567539
Calculated fields are handled by the database engine since they have to be available regardless of what FE is accessing the table.  Because of this they can only work with columns from the same table where they are created AND they cannot use anything that is not an SQL function.  So, something like Nz() is VBA and so would not be available.

Given the limitations, I see no benefit to calculated fields on tables since they are quite easy to include in a query.

Running totals make no sense in a relational table.  Tables are not spreadsheets.  They are not lists.  They have no inherent order.  So who's to say what record comes before any other record at any particular time. Not to mention the fact that each table row is an independent entity and cannot depend on any other row.   You can easily create a running sum in a report.  It is much more difficult and costly in a query.  Other RDBMS' include a RunningSum function but ACE does not.  Relational databases are based on set theory which doesn't say anything about order.  Therefore, if order is important to a process (it is critical to a running sum) you must sort the recordset by a unique identifier to actually make a running sum work in a query.
0
 

Author Closing Comment

by:Jass Saini
ID: 40577834
Thanks for the Help
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now