Solved

Calculated Field in a table

Posted on 2015-01-23
13
125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 85
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 48

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

691 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