Calculated field in a Table. Possible only in Query/Report?

[EagleOne]

2003

What to add a field into a Table1. Should be simple but I can not get it via Expression Builder.

Have:

Field Name Type
AMT NUMBER

Want to add:

AMTRev NUMBER


AMTRev is AMT*-1 If possible what formula syntax should I use?
I attempted =[AMT]-1 which I placed in the Default Value property.


Can one add a field to a table via expression or must it only occur in a query/report?

TIA EagleOne

 

[Rick Brandt]

2003

What to add a field into a Table1. Should be simple but I can not
get it via Expression Builder.

Have:

Field Name Type
AMT NUMBER

Want to add:

AMTRev NUMBER


AMTRev is AMT*-1 If possible what formula syntax should I use?
I attempted =[AMT]-1 which I placed in the Default Value property.


Can one add a field to a table via expression or must it only occur
in a query/report?

TIA EagleOne

Calculated values do not belong in tables. Use a query, form, or report.

 

[EagleOne]

99% of the time I'd agree. This is that 1 time.

Thanks for sharing your knowledge and time!

2003

What to add a field into a Table1. Should be simple but I can not
get it via Expression Builder.

Have:

Field Name Type
AMT NUMBER

Want to add:

AMTRev NUMBER


AMTRev is AMT*-1 If possible what formula syntax should I use?
I attempted =[AMT]-1 which I placed in the Default Value property.


Can one add a field to a table via expression or must it only occur
in a query/report?

TIA EagleOne

Calculated values do not belong in tables. Use a query, form, or report.

 

[Clif McIrvin]

99% of the time I'd agree. This is that 1 time.

I agree with Rick, but ** You ** are the one who knows your situation.

To do what you describe, I'd:

1. for existing data write an update query, placing =[AMT]-1 in the
update to field of AMTRev in the Query design grid.

2. for new data being added through the user interface, put =[AMT]-1 in
the control source of the AMTRev control of your data entry form.

Uhh, you are using a form for data entry, right?
--
Clif
Still learning Access 2003

Thanks for sharing your knowledge and time!

2003

What to add a field into a Table1. Should be simple but I can not
get it via Expression Builder.

Have:

Field Name Type
AMT NUMBER

Want to add:

AMTRev NUMBER


AMTRev is AMT*-1 If possible what formula syntax should I use?
I attempted =[AMT]-1 which I placed in the Default Value property.


Can one add a field to a table via expression or must it only occur
in a query/report?

TIA EagleOne

Calculated values do not belong in tables. Use a query, form, or
report.

 

[a a r o n _ k e m p f]

you can only utilize 'calculated field in a table' using SQL Server
Access does not support this functionality.

for example, if you had a field such as 'ExtendedPrice' it would be
Qty = 3 * Price = $1.29.

SQL Server supports this type of calculation-- MS Access does not

 

[Rick Brandt]

99% of the time I'd agree. This is that 1 time.

Thanks for sharing your knowledge and time!

I think you are mixing concepts.

The concept of storing the result of a calculation (however that calculation
is performed) in a table is the one that is mostly not a good idea but which
has the occassional exception to the rule.

The concept of creating a field in a table's design that is automatically
the result of a calculation (this is what you specifically asked about) is
100% of the time not possible in an Access (Jet) table. It can be done in a
SQL Server table, but not in a native Access table.

 

[So Sorry For Poor Aaron]

you can only utilize 'calculated field in a table' using SQL Server

and in every cheap two-bit desktop database that ignores relational database
principles. Why would anyone need to store redundant data when they can
calculate in the Query used to retrieve it?

Access does not support this functionality.

Access supports this functionality _where it belongs_; that is, in Queries,
not in Tables where it is a violation of relational design principles.

What kind of rockhead, blockhead, crackhead are you? Oh, I know, you are
the kind that Big Bruce, Big Bubba, and Big Barney love to love. Poor "Big"
brothers, they really get hard-up for entertainment after being in for so
long, don't they? But, hey, it was really considerate of you to cheer them up
before Daddy's dough bought you out.

SQL Server supports this type of calculation

And DOS dBase, and . . .

-- MS Access does not

And, rightly so.

 

[a a r o n _ k e m p f]

because.. uh you're not storing it.. you're calculating it.. but you
don't need to copy and paste the formula for calculating it in 100,000
different places.

you can reuse a formula like that in a SQL Server table, but not in
Access
thanks

-Aaron

 

[a a r o n _ k e m p f]

having the same calculation repeated in 20 different queries-- that is
supported in SQL Server.

but keeping them in one place-- the optimal place to put them-- is
alot simpler, by any defintion