Table/Query Data

[Drake12]

I have a simple database with just one table and query. I added all fields to the table and all the fields to the query and also added the calculated fields that I need. I did that to make a form that had updated calculated fields when a user is entering data. I see now that the table has zero's in the fields that are being calculated in the query. Should I just take those fields out of the table or do they have to be there? Or is there a way that I can get them updated with the calculated information? I thought the table was the main source in a database and needed to have all the current field data. Just seems like I might have problems down the road if a lot of fields have zero's in them. I know that the data is in the query, but maybe I should be setting up the database in a more correct manner. Just starting out in Access and this will be a large database one day and need to start it out the correct way.

Thanks for patience!

 

[BNPMike]

If you have a calculated field in a query it should update itself as you update the field(s) it is derived from. Don't store calculated fields in the database as a general rule. There's no point and you risk getting an inconsistency between the data stored and the source data.

 

[formerTexan]

BNPMike is absolutely correct about leaving calculated values out of your tables. I think the only reasonably widely used exception is for accounting audit or closing purposes.

But a note on your zero's. If you have a look at your table in design view, you will notice a default value property for some fields. It is your option as to whether you wish to retain this as the default, leave it blank or set something else.

It really depends on the type of data and the use. If you are doing statistical analysis, then you definitely want a blank (null) rather than a zero. However, for accounting data, a zero is a perfectly acceptable default.

A zero is treated as a value (as in a zero on a test), a null is not a value (no one showed up to take the test).

Cheers,
Bill