Auto Fill Fields: Best Way?

[luke242]

Hello,
I am an appraiser and I am making a database to track files that I complete. I need to fill in 28 fields for each file I do (stupid goverment). Many of the fields can be determined once a corresponding field has been filled in. Others fields can be estimated when corresponding fields have been filled in. My question is how should I best going about taking advantage of this?

My idea:
Fields that can be determined for certain (called determined fields) once a corresponding field (called determining field) has been filled in can be stored in a separate tables (one table for each determined field) that are related (one to many) to the determinging value in my main table. I think I can than produce a query that will place both the determining and the determined values on one line for each file that I do. Is there a better way to handle this??

More complicated is how deal with the fields that can be estimated (estimated fields) once some corresponding field (determining field) has been filled in. Once the determining field has been filled out, the estimated field may have to be changed, though often it will not need to be. This means that they can not be stored in a seperate related table as the determined fields can. My alternative to it code VBA that detects a change in the determining field and then fills in the estimated field, which could be changed if it is incorrect. Again, is there a better way to handle this??

Please speak simply, I am relatively new. Thanks for the help,
~Luke Campbell

 

[BNPMike]

What you appear to have is a relationship that says "For field X, value A means Field Z is 2, value B means Field J is 7, ..." etc. For this situation you don't need to store Field Z or J at all. Just generate it from your table of dependancies whenever you print a report etc. The table will be
(SourceFieldName, Value, TargetFieldName,TargetfieldValue)

When it comes to the fields you can change then you need to put them on a form. With either VBA or Macros, respond to an event leaving a determinant field and use a query to change the value of the dependant field. Bind the dependant field to your data table so that when you leave the form, the database is updated either with the defaulted value or the one you have subsequently overwritten it with.

 

[luke242]

Great idea not storing the data that is predetermined. Eliminated 5 fields. I wrote VBA for the other situations and it all works beautifully.