Calculated values in tables vs. in form fields

[Yair Sageev]

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is this
possible?

The reason I am asking is that it seems impossible to run a query on values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or MSDE,
handle the updating of calculated values if the original values are changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody goes and
changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called "ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest CostPerWorkers for
a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.

 

[Tom Ellison]

Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is this
possible?

The reason I am asking is that it seems impossible to run a query on values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or MSDE,
handle the updating of calculated values if the original values are changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody goes and
changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called "ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest CostPerWorkers for
a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Yair Sageev]

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.

 

[Yair Sageev]

Ignore my last post. The answer is "read the help".

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.

Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

goes

and

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Yair:

Whether to use a calculated column in a form or report as opposed to
having a calcultion in just one view is a judgement you should make.
I do not recommend you always put it in a view.

Some calculations are just not subject to change. If you extend price
by quantity to get an extension, you can be reasonably sure it won't
ever change.

Other calculations are what we term "business rules" that reflect a
choice in how the business is run. This generally implies that
management could change their mind at some time, or that, if the
software is ever applied to a different business, their choices might
be different. Making sure such calculations are only recorded in one
place, and that it's easy to find that place, is a very good idea.

I recommend that going to the extreme of having NO calculated controls
on forms is not really necessary. I'm not sure if there's any way it
would actually hurt to do so. But, in any case, I don't feel this
should be a rule.

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.

Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.



Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Yair Sageev]

I appreciate your thoughts. We are indeed talking about factoring the
database into reusable components, but we are also talking about the ability
to make queries on aggregate calculated data and deduce risks/problems.

If I have a calculated field in a form or report called
CostPerWorkerPerProjectPhase and I then wish to figure out which project
phases tend to be the most costly and which types of projects have the
highest CostPerWorker per project phase, if that calculation is the control
source for a form control, it *cannot* participate in a query that doesn't
filter for a given form. (Right?). It is only good for display in either
data entry mode or report mode, but it isn't business data.

I am choosing the view approach at this time because I have not migrated to
MSDE and which to be compatible with both in the future, otherwise I would
go with the calculated table column approach, as you mentioned.

From the point of view of the whimsical nature of business logic, that
whimsicality persists in equal amounts whether or not the logic resides in a
form control (which will have to be recoded when the logic changes) or in a
view (which will need to be recoded if the logic changes). The advantage of
the view approach is that if multiple form/report fields bind to the column
in the view, they will automatically reflect the updated logic once the
single instance of the column in the view is recoded.

Dear Yair:

Whether to use a calculated column in a form or report as opposed to
having a calcultion in just one view is a judgement you should make.
I do not recommend you always put it in a view.

Some calculations are just not subject to change. If you extend price
by quantity to get an extension, you can be reasonably sure it won't
ever change.

Other calculations are what we term "business rules" that reflect a
choice in how the business is run. This generally implies that
management could change their mind at some time, or that, if the
software is ever applied to a different business, their choices might
be different. Making sure such calculations are only recorded in one
place, and that it's easy to find that place, is a very good idea.

I recommend that going to the extreme of having NO calculated controls
on forms is not really necessary. I'm not sure if there's any way it
would actually hurt to do so. But, in any case, I don't feel this
should be a rule.

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.

Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is this
possible?

The reason I am asking is that it seems impossible to run a query on values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or MSDE,
handle the updating of calculated values if the original values are changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody

goes
and

changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called "ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest

CostPerWorkers
for

a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.


Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Yair Sageev]

There may be some confusion (rereading earlier posts).

I am going with the approach of writing a large query with numerous
calculated columns. Yes? We are not talking about writing individual
queries for each calculation, although, I can see the use for that too....

Dear Yair:

Whether to use a calculated column in a form or report as opposed to
having a calcultion in just one view is a judgement you should make.
I do not recommend you always put it in a view.

Some calculations are just not subject to change. If you extend price
by quantity to get an extension, you can be reasonably sure it won't
ever change.

Other calculations are what we term "business rules" that reflect a
choice in how the business is run. This generally implies that
management could change their mind at some time, or that, if the
software is ever applied to a different business, their choices might
be different. Making sure such calculations are only recorded in one
place, and that it's easy to find that place, is a very good idea.

I recommend that going to the extreme of having NO calculated controls
on forms is not really necessary. I'm not sure if there's any way it
would actually hurt to do so. But, in any case, I don't feel this
should be a rule.

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.

Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is this
possible?

The reason I am asking is that it seems impossible to run a query on values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or MSDE,
handle the updating of calculated values if the original values are changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody

goes
and

changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called "ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest

CostPerWorkers
for

a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.


Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Yair:

I can see no reason not to put many calculations in one view. In
fact, that's just what I would expect.

There may be some confusion (rereading earlier posts).

I am going with the approach of writing a large query with numerous
calculated columns. Yes? We are not talking about writing individual
queries for each calculation, although, I can see the use for that too....

Dear Yair:

Whether to use a calculated column in a form or report as opposed to
having a calcultion in just one view is a judgement you should make.
I do not recommend you always put it in a view.

Some calculations are just not subject to change. If you extend price
by quantity to get an extension, you can be reasonably sure it won't
ever change.

Other calculations are what we term "business rules" that reflect a
choice in how the business is run. This generally implies that
management could change their mind at some time, or that, if the
software is ever applied to a different business, their choices might
be different. Making sure such calculations are only recorded in one
place, and that it's easy to find that place, is a very good idea.

I recommend that going to the extreme of having NO calculated controls
on forms is not really necessary. I'm not sure if there's any way it
would actually hurt to do so. But, in any case, I don't feel this
should be a rule.

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.


Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is
this
possible?

The reason I am asking is that it seems impossible to run a query on
values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or
MSDE,
handle the updating of calculated values if the original values are
changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to
have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody goes
and
changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called
"ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest CostPerWorkers
for
a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.


Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Yair:

That's what I'm talkin' about!

I appreciate your thoughts. We are indeed talking about factoring the
database into reusable components, but we are also talking about the ability
to make queries on aggregate calculated data and deduce risks/problems.

If I have a calculated field in a form or report called
CostPerWorkerPerProjectPhase and I then wish to figure out which project
phases tend to be the most costly and which types of projects have the
highest CostPerWorker per project phase, if that calculation is the control
source for a form control, it *cannot* participate in a query that doesn't
filter for a given form. (Right?). It is only good for display in either
data entry mode or report mode, but it isn't business data.

I am choosing the view approach at this time because I have not migrated to
MSDE and which to be compatible with both in the future, otherwise I would
go with the calculated table column approach, as you mentioned.

From the point of view of the whimsical nature of business logic, that
whimsicality persists in equal amounts whether or not the logic resides in a
form control (which will have to be recoded when the logic changes) or in a
view (which will need to be recoded if the logic changes). The advantage of
the view approach is that if multiple form/report fields bind to the column
in the view, they will automatically reflect the updated logic once the
single instance of the column in the view is recoded.

Dear Yair:

Whether to use a calculated column in a form or report as opposed to
having a calcultion in just one view is a judgement you should make.
I do not recommend you always put it in a view.

Some calculations are just not subject to change. If you extend price
by quantity to get an extension, you can be reasonably sure it won't
ever change.

Other calculations are what we term "business rules" that reflect a
choice in how the business is run. This generally implies that
management could change their mind at some time, or that, if the
software is ever applied to a different business, their choices might
be different. Making sure such calculations are only recorded in one
place, and that it's easy to find that place, is a very good idea.

I recommend that going to the extreme of having NO calculated controls
on forms is not really necessary. I'm not sure if there's any way it
would actually hurt to do so. But, in any case, I don't feel this
should be a rule.

Thanks Tom,

I will use a view to perform the calculation, reference the view, and stop
using calculated form/report fields.

Just one follow up question:

Do I need to have column placeholders in the underlying table(s) to
represent fields in the query, or can I give query fields arbitrary names?
I would rather assign "temporary" names to the calculated columns in the
query. I tried typing in "hello" as the field name. Access converts this
to "Expr1: [hello]". I then set the criteria to be budget*200000. Access
prompts me to enter "hello". I was hoping that that running the query
would show a column called "hello" with 4567789999 as the value.

Again, thanks.


Dear Yair:

There is a calculated column capability in MSDE.

These values are not stored, only the formula is stored. So, it works
just like a query - the result is calculated at run time and never
stored.

For most purposes, this is the same thing as calculating it in a
query. However, if this calculation is used in many queries, it can
be more modular to use a calculated column. If the formula ever
changes, then you have only one place where you need to change the
formula and it is then changed across the whole application. This
allows for some improvement in the modularity and maintainability of
your database.

The same advantage can be had in MSDE by creating a view that performs
the calculation and then using that view every time you need the
calculation. This way the formula is also only in one place. In many
cases, the view is a more powerful way to accomplish the same thing.

For your "Project Phase" problem I think I'd use a view that performs
the calculation. It would be pretty straight forward I think.

The biggest thing is to train the brain to think modular. The rest
will follow. DO try to avoid having the formula in your database in
more than one place.

Also, if you're using Jet as your database engine, having a singe
select query that does this work is a good idea. You can then base
all other queries that need the calculation on that query.

Hi,

Sorry for the cross-post. I think I might have what could be a difficult
question:

We have numerous form and report fields whose values are calculated from
other values stored in tables.

I would like to move these calculations to the tables themselves -- is
this
possible?

The reason I am asking is that it seems impossible to run a query on
values
that aren't in the database proper, even though those values are just as
important as the stored values. We need to calculate those values, since
the user shouldn't have to manually calculate them. But if they can be
moved to a column in a table, how does the database, be it Access or
MSDE,
handle the updating of calculated values if the original values are
changed.

To be more clear, suppose I have a table column called "Budget" in the
"Project" table. This number is entered by the user. I would like to
have
another column in the table called CostPerWorker, which isn't manually
edited, and whose value is "(Budget - Materials) / NumberOfWorkers".
Supposing I could set this up -- what happens if, one day, somebody goes
and
changes the "Budget" value? Do the dependent fields get updated?

What happens if there are 20 divisions to the project called
"ProjectPhase",
each a record foreign-keyed to Projects, and we would like to know the
CostPerWorker of each ProjectPhase, and somebody changes the value of
"Budget". Is the database smart enough to update CostPerWorker for each
ProjectPhase *immediately*? If I change "Budget" and run a query asking
"what are the top 10 ProjectPhases that have the highest CostPerWorkers
for
a given project?", will the result set be accurate?

How do people deal with this type of situation?

Much thanks.


Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts