calculated fields

[maxmangion]

let's say i have a table with 3 fields: amount1, amount2, total. the total field should calculate the sum of amount1 and amount2. to achieve such a thing i usually create a query and then make the total field a calculated field. then i base any subsequent forms/reports on the query rather than on the table.

is this the correct procedure or such a thing should ideally be done by using the SetValue in Macros so that the total field in the table would not appear as 0 ?

 

[FoFa]

Typically you should not have a total field in the table. Usually you calculate these "on the fly". There are exceptions, but using a query and basing everything off of that is a better way to do it.

 

[Mile-O]

You shouldn't have a field for Amount2 or a Total.

If you are going to have multiple amounts then you need a new table to manage this as you have identified one-to-many relationship.

Get a further table for Amounts (tblAmounts)

AmountID (PK)
MainID (FK _ I don't know what the table Amount was originally in's PK is)
Amount (Currency)

Now you can query based on the MainID field for your case and simply sum the contents of the Amount field within the query.

 

[maxmangion]

actually i typed amount1 and amount2 for the sake of my thread ... in the example which i am referring to i have some fields where i keep track of stamps which are duplicates ... however, some of the stamps are still with paper therefore they need to be cleaned so i have three fields Cleaned, ToBeCleaned and Total (the total of Cleaned & tobecleaned) ... so do you think i should create the extra table you mentioned ? ... because i have those 3 fields in my main stamps table ?

 

[FoFa]

If you have just these 2 static fields, most likely not (but you should do away with total). But you have to remember if you decide to add (Partially cleaned, needs cleaning but I don't feel like it and needs glue) then you have to add fields to your table rather than say changing a status in a seperate table. But from the sounds of it I would say you are OK the way you are handling it if this is for your use and not a client where it might need additions (as I said above).

 

[maxmangion]

thx for the info! i have been using it this way for quite some time and i had no problems yet. the only reason why i was asking this question is, because i was readin somewhere online (i can't remember where) and it says that such situation may be handled by macros SetValue so that the total field in the table does not appear 0.

i do not like using macros very much, but i just wanted to have the idea of the experts in here

Thx!

 

[bdhtexas]

What about...

I have a similar situation.

Balance Adj Reductions
Less fee credits (amount we pay the vendor)
Savings (amount we'd like to auto calculate if possible)

I tried to run a query but it returned no results, even though I had 8 records that met the criteria. I placed the formula in the criteria field and kept getting no results. I then placed it in the Field area and got another error. I've read the book and the help sections. NO LUCK.

=[Balance Adj Reductions]-[Less fee credits]

I have this in the criteria area in the Savings column.

Please help.

Thanks,
Brian

 

[maxmangion]

you shouldn't make that formula in the criteria section. you should place that as a new column in you query. it should be similar to the following:

Expr1:[Balance Adj Reductions]-[Less fee credits]

where Expr1: can be any valid name for your new calculated field ( note that the : should always be present )