Question calculated fields vs expressions

I'm developing a simple database for some relatives. I'm replacing a decade old MS Works database which is more or less a flat file with everything in one table with a different database for each of the main entities - an utter mess.

Anyway -

I've set up a prototype form for the front-end. There are two fields named Deposit and Balance. They are added together to give Total.

I've been reading that using a calculated field is generally a bad idea for reasons I understand. Therefore all the fields are set to number. I can see that I can set the Control Source for Total to be an expression which performs the same calculations as a calculated field would.

Is this good or bad practice? Is it safe to use instead of a calculated field?

It seems to work fine, the fields are added and appear in the Total field which is then stored as Number instead of a calculated field. But, I'm smart enough to developers (not that I should call myself one) should think long term.

Still to me, it's doing the exact same thing with the exact same result.

Am I doing this the right way?

-----------------------------

I currently use two lookup tables, one stores payment methods, the other stores food categories. I could also set the display control to a combo box and use the row source populate the box (via the wizard) instead of the lookup table.

Which is the better practice?

thanks chaps!

You should seriously avoid storing computable data in a table field. The exception is if some part of the computation is NOT stored so you need to keep the result instead.

First, a select query reference does the same thing as a table reference. Both supply a recordset. Therefore, you can base a form off a query. This works because...

Second, if everything you need is either stored or instantly computable through use of a query, basing a form or report off the query works equally well and yet saves space. Now, if this totals table is something really small like 20 records, it may be a case of "who cares" - but if the table can get larger or you want to learn to do it right, YOU care. Then, you would want to base the form / report off the query.

Since you are asking which is better, it would help if I tell you why. When you are working with a recordset, you are pulling data from a recordsource through a memory buffer. In Access, memory buffers used for disk data are limited to about 2K bytes, which means no record can exceed that number. But if you can fit multiple records in a single buffer, you can do searches faster. The more records you fit, the faster the search becomes. Therefore, anything that minimizes the size of the data chunk being loaded from disk makes the process of data searches faster because smaller records in a fixed buffer means more records in a single disk operation.

Now, it is true that disk prices and memory prices are dropping fast. (Actually prices are almost constant but capacity is going up, so cost per unit is what is dropping.) Therefore, you could argue that you have plenty of room for whatever you are storing. But it isn't the static cost to store data that hurts. It is the cost to repeatedly handle that data that will eat your socks for you.

i converted a works database for a client - it really is suitable for a rudimentary application only. The data had to be renormalised, and of course there ended up being a lot of data errors - the "same" item spelled differently - which meant it needed a lot of tidying before it could be used in a proper normalised way.

now - there is no "calculated field" - if you have a field to store a calculation, then its just another ordinary field

the thing is if you store say, a price for an item and a quantity sold, and you need to then add on VAT - you DONT necessarily need to store ALL of these

Price (raw data)
Quantity (raw data)
sub-total
VAT rate (raw data)
Vat amount
line total

since you can calculate all the values form the raw data only - and furthermore if your raw data gets amended at any time, you need to remember to update the calculated figures. now in the case of an invoice you might STILL decide to store the totals anyway.

so the same applies in your case. You have a balance and a deposit. so you dont need to store the balance after the deposit. in fact you dont really need to store the balance before the deposit. what you would normally have is this:

a master table, that stores the blaance at a given point.
a transactions table that stores each transaction

you then calculate the balance at any time by adding together, the initial balance, and the otal of all the relevent transactions.

this way, if you need to delete a transaction for some reason - then you dont need to change anything - you dont have to search for all the items that will be affected by the deletion - because there arent any!

---------------
the trouble is to get into spreadsheet thinking - where you consider each row, and calculate a total to be used by the next row. Its a shift in concept terms, but although a database table looks like a spreadsheet, this is in fact exactly the concept to avoid. As far as a database is concerned, the row order in a table is not significant at all. The way to think about it is that (in eqivalent terms) you are selecting a number of rows in which you are interested, totalling those rows, and using the totals only. That way the order of the items in the selection doesnt matter.

I hope this makes sense