Calculated Field (if/then) Help

[agent-]

Hello everyone!

I have a few questions regarding an Access 2007 DB that I am developing. I am wondering if these are possible in Access 2007 or if I need to use something else.

I need to add a field in one of my tables that will calculate between fields in the table. I need this to be dependent on a status field, since the calculation may change.

psuedo code:

if(table.field = fixed)


Calculated_Field = Fixed_Cost

if(table.field = indexed)
Calculated_Field = Fixed_Cost + Index

etc..

I noticed 2010 has the calculated field option in the table design, but 2007 doesnt. Also, I know you can do calculations in queries but this needs to be in the table since there are different calculations based on status.

Thanks a lot!

[agent-]

Or if you can just point me towards how to have a field calculate a+b fields then I should be able to figure it out

[orange]

Most people will advise you to NOT store a calculated field in a table.
If you can calculate the field then you can reproduce the calculation at any time.

Can you give some status codes and some values and we'll try to work out an expression that could be used?

[agent-]

Thanks for the reply orange!

Yea I saw that most people advise against doing calculated fields. I have never done this before and usually rely only on my queries to create calculated fields, but this one is somewhat different and the calculation equation may be changing. Lets just try these for an example. Total cost would be the calculated column.

Status | Fixed Cost | Index | Total Cost
------------------------------------------
Fixed ----20 -------------------- 20
Indexed--15--------- .05 -------15.05
Fixed --- 30--------------------- 30
Indexed -20--------- .10-------- 20.1

Thanks a ton!

[orange]

This calculation should work.

Sub mar29()
Dim status As String
Dim fixedcost As Double
Dim IndexToApply As Double
Dim TotCost As Double
fixedcost = 200.5
IndexToApply = 0.88
status = "indexed" '"Fixed"
TotCost = IIf(status = "Fixed", fixedcost, IIf(status = "indexed", fixedcost + IndexToApply, 0))
Debug.Print "TotCost is " & TotCost
End Sub

'************************
Debug.Print "TotCost is " & TotCost
End Sub

Depending on how you will use it, you may need
to open a recordset and use the
IIf(status = "Fixed", fixedcost, IIf(status = "indexed", fixedcost + IndexToApply, 0))


Hope this helps.

[agent-]

Thanks orange, but i have a few questions. Does it matter where I put that in the visual programming module? also, will this calculate for every row without manipulating? the problem is i will be having different fixed costs and indexes for each account. I do not want to have to input this information every time i want it to calculate. Let me know if I misread your post or there is a way to fix this

thanks

[orange]

If you have a table Accounts, you can probably do this with a query.

[agent-]

Ok what if i just separate tables based on Indexed or Fixed. Is there anyway for the calculated column to just add column x + column y for every record and post that into column z?

[orange]

What do the tables or query look like where these fields originate for this calculation?
Hopefully they are in a table or query, and if so a query can be written to do all the calculations.
Do you really need the calculated TotCost in a table? in a table?

The reason I ask is because if any of these values will be changed, the calculation will be wrong, unless it is recreated from the latest data.

[agent-]

Well I can create the query for the calculated field if i decide to do separate tables for indexed, fixed etc. I will have to think of a way to create a single calculation that can be used for indexed and fixed. I was thinking if i just leave the index blank, i can still do the same calculation for the fixed since it would be fixed + indexed and indexed = 0;

I also can create calculated column in SharePoint 2010 since the Access database will be linked up with that, but I am trying to not go that route. This is still in the logical stages right now. I am just trying to figure out what I can do and what I can not do before I promise it to someone.

Thanks for all your help orange!

[orange]

I'm trying to find out where the fields exist now.
You can do most what you want with a query.

Are you familiar with Queries in Access?

What tables, fields are involved in the data you want to process?