manually update field - want to create calc. but probl prev. table

[babs]

currently we have a contract amount field for a table - many Many
queries,forms, reports are presently using this table- we are manually
recalculating this contract amount - I have created a query to
update-recalculate the contract amount (based new workorders)- BUT!!!! - now
I don't know how to handle past data - using this contract amount from the
table - always query Past - present date - what am i missing- on how to
handle???

Also don't know of a good way to find out all of the queries - reports,
forms using this contract amount field - tried object dependencies says can't
do it??

thanks soo much,
barb

 

[Tom van Stiphout]

On Wed, 2 Dec 2009 05:44:01 -0800, babs

You write - like - you think - which is - hard to - follow.
If ContractAmount is a calculated value, it does not belong in a
relational database; rather it should be calculated in a query if ever
needed. You are now confronted with the nasty side-effects of not
following proper design.
Say you succeed in recalculating the amount. When will you run this
procedure? You'd have to do it ANY time ANY of its underlying values
change. Lots-o-work.
Anyway, assuming this is water under the bridge, you need an Update
query that updates ContractAmount based on its underlying values. It
will use a Totals query with a Sum to calculate the sum of the line
items, or whatever your exact situation is. You may want to consider
hiring someone to help you with this. "Microsoft Solution Provider" in
your yellow pages is a good place to start.

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

Usually you want to calculate data in calculated fields in queries, reports,
and forms instead of storing calculated data. However there are exceptions
with things like invoices which are fixed at a point in time. It sounds if
you have this issue.

1. Store finalized data in a seperate table so that it doesn't get changed.

2. Use something like a Completed field of the Yes/No datatype to show if
that record is complete. Then use this field as the criteria in any update
queries so as not to disturb any completed records.

3. Keep a table of items and prices by date range. That way you can still
calculate old records by the values on that date. This method is VERY
difficult to do right.

4. Print to paper or PDF files with a record is complete. That way you have
something to go back to if there is a question on a particular contract. It's
old fashioned, but that's how it was done back in the day.

For your second question, Rich Fisher's excellent Find and Replace add-in
might be just the ticket. If you register it (one of the best $37 I've ever
spent), the cross-reference report can find what queries use which reports
and forms. It's a good way to find orphans. I think that it might be able to
find things at the field level also.

http://www.rickworld.com/

 

[babs]

thanks tom - yes - I inherited the database and they are manually going BAck
to the Table and recalculating the Contract amount - so I created a
calculation in a query but ALL reports/forms/queries and everything else!!!!
references THAT contract Amount from the TABLE -not my calc. one in the
query- I seems tooooo complicated to Find everything that is Using this
contractamount field

I think your advice to prob. turn my calculation into an UPDATE query of the
original Table - with the contract amount field(it is Better than Manually
doing it like today)- I could Tie it to a command button where they add the
work orders - Just want to make sure they don't UPDATE one too many times -
any tips??? on that?

thanks for the advice!
Barb

thanks