Solved

Calculated Field in a table

Posted on 2015-01-23
13
123 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…

685 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