Need Calculated Fields in Table View

[JB]

Hi,

I currently store treasury bill information in Approach 9 and have the DB
working just like I want it except that mouse scrolling does not work with
Windows XP.

To remedy this I started a new DB in Access 97.

The problem is getting two calculated fields in the table view. I know this
can be done with a query but I want it in the table as I need to view all
fields at once and be able to sort on any field.

My workaround for now is to enter the data in Excel and link the spreadsheet
to Access. That works fine but there must be a more efficient way and I
would appreciate any tips.

Thank you.

Mike

 

[Guest]

Hi Mike,

You should not attempt to store a calculated result in a table. Doing so
violates database normalization rules. Here is a paper by Database Design
expert Michael Hernandez:

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

It includes the following quote on page 23:

"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."


You should do your calculations in a query. You can display all fields from
a table in a query, and sort to your heart's content.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Mike,

You should not attempt to store a calculated result in a table. Doing so
violates database normalization rules. Here is a paper by Database Design
expert Michael Hernandez:

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

It includes the following quote on page 23:

"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."


You should do your calculations in a query. You can display all fields from
a table in a query, and sort to your heart's content.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jesse]

You can use a query to do it just don't include any filter criteria for
it.

 

[Jesse]

You can use a query to do it just don't include any filter criteria for
it.

 

[JB]

I was aware that it's not recommended but it works so well in Approach and
the DB is so simple I figured I could get away with it.

But you say I can view and sort all fields in a query so that will solve the
problem.

Thank you very much Tom.

 

[JB]

I was aware that it's not recommended but it works so well in Approach and
the DB is so simple I figured I could get away with it.

But you say I can view and sort all fields in a query so that will solve the
problem.

Thank you very much Tom.

 

[JB]

You can use a query to do it just don't include any filter criteria for
it.

OK...I am going to give it a try. Thank you Jesse.

 

[JB]

You can use a query to do it just don't include any filter criteria for
it.

OK...I am going to give it a try. Thank you Jesse.

 

[Joseph Meehan]

I was aware that it's not recommended but it works so well in
Approach and the DB is so simple I figured I could get away with it.

But you say I can view and sort all fields in a query so that will
solve the problem.

Thank you very much Tom.

Do try to normalize the data. We don't recommend this just because it
is a rule, we recommend it because we have all seen the results of what
happens when you don't. Most of us have thought we knew better before we
learned.

 

[Joseph Meehan]

I was aware that it's not recommended but it works so well in
Approach and the DB is so simple I figured I could get away with it.

But you say I can view and sort all fields in a query so that will
solve the problem.

Thank you very much Tom.

Do try to normalize the data. We don't recommend this just because it
is a rule, we recommend it because we have all seen the results of what
happens when you don't. Most of us have thought we knew better before we
learned.