Jass Saini
asked on
Calculated Field in a table
I have a calculated field in my table and it's not returning any results
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?
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?
ASKER
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
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
You can use Nz() to convert nulls to zero. For example, instead of:
[FieldX] + [FieldY]
use
Nz([FieldX], 0) + Nz([FieldY], 0)
[FieldX] + [FieldY]
use
Nz([FieldX], 0) + Nz([FieldY], 0)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Jeff,
Why would you not use it?? I am trying to keep a running total for each record in my table.
Why would you not use it?? I am trying to keep a running total for each record in my table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
ASKER
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
@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
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
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.
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.
ASKER
Thanks for the Help
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.