HELP I need multiple calculated fields in one table

[dhammond1221]

I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.

I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:

1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill

Here are my excel formulas that for the life of me I can't get to work.

Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))

Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))

Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO"),"NO"))


Can I have calculated fields that refer back to a calculated field?

This is soo confusing and I am running out of time

 

[plog]

First, Access isn't Excel--You don't store calculated values in a table, you calculate them in a query. That way, whenever you run the query you have the most up to date calculated values.

I appreciate your posting your Excel formulas, but I work better with data. Can you provide some sample data from your table, and what the resulting data should be for those 4 values based on that sample data? Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Sally, 3/2/2008, 56
David, 4/1/2007, 78
Larry, 3/15/2006, 1

 

[dhammond1221]

I am not sure if you received my last response. I am new to this website and appologize for my ignorance.
I have too many columns on the excel w/s and could not paste in soo I have attached an example of my excel w/s.

Columns AG, AH & AI are the calculated fields I am trying to create in access

Donna

 

[plog]

No I didn't get it. Try zipping it and then posting it. Or a screenshot would work too.

 

[dhammond1221]

I have too many columns to do a screen shot. I zipped the file and have attached.

Can't tell you how much I appreciate you helping me. :banghead:

 

[plog]

Your code is so nested and complex (in the sense that there is a lot of it) it needs to be written so that a human can follow it. What you should do is build a custom function in VBA, pass it the data it needs and then have it return the value you want. Something like this:

Function get_AccountBalance(AccountType, Elected, Deposit, Claim)
    ' takes account type and 3 values to determine Account status

ret = "Negative Balance"

' put your logic here


get_AccountBalance = ret

End Function

Barring that, your code is also simple (in that its not using any complex functions) here's a few tips that you can use to convert your Excel code to Access.

Make a query, bring in your table and then bring down the relevant fields ([ACCOUNT_TYPE], [ELECTED_AMOUNT], [TOTAL_DEPOSIT_AMT], [TOTAL_PAID_CLAIM_AMT]). Then make a calculated field based on your code:

AccountBalance: IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))


Now, that's not going to work because Access needs field names, not cells. So Replace your cell references to the appropriate fields (e.g.V2=[ACCOUNT_TYPE]). And then change your IF to IIF. That should make the above code work.

 

[dhammond1221]

Thank you, Thank you. I will try that and let you know how it goes.

 

[dhammond1221]

I am getting "#Error in" the calculated field that should have Zero, available or negative balance as the result. I have no clue how to fix and I am sure it is simple.

 

[plog]

If I was the type of person who could debug code and you wanted me to debug code, what should you provide me so that I can debug code?

 

[dhammond1221]

Again, I feel I have to apoligize for my ignorance. I am new to this and have no idea what you last message means?

I have created my calulated field in my table and a query to pull specific data. I am uploading more data to test my calulations.

Thanks for you time.

 

[The_Doc_Man]

Here is food for thought, and also a suggestion for reading on the web or in Access help files. It won't solve your immediate problem, but it might start you down the right path for future design issues. I offer this thought ONLY because you identified yourself as being very new to Access. It might be helpful.

When you build a table, it holds data, often but not always statically. Calculations can be based on the data you have. However, storing calculated data in a table is generally NOT a good idea because of the concept of data changing over time, and the fact that you have to revisit the table to update the computations frequently in order to keep the computations current with respect to your data.

The preferred solution is to put the calculations in a query, not a table.

Why does this work? Because... anything else you do (like forms or reports or exporting table contents) works on RECORDSETS, not tables or queries. A recordset can equally be derived from a table or a query. There are exceptional cases that prevent you from updating query-based forms when you do lots of computations, but a report can be fat, dumb, and happy with a messy query-based recordset. Therefore, putting computations in a query is the right answer for reports. You can also do computations in forms if you have to do so, but you don't need to store anything for the results of a computation.

There is also this to consider. If you can create a function using VBA, you can call that function from a query and can display the results exactly as you would display anything else - as a number, as text, or as the phase of the moon if that was your computation. You cannot call a function from a table entry. Therefore, if you have a complex computation that needs to resort to functions, you simply cannot do that call from a table. You can ONLY do that from a query.

This is the major difference between Excel thinking and Access thinking. In Excel, you don't see the data cells - even based on formulae - as having any differences other than formatting. In Access, that difference is incredibly important and readily apparent.

To solve your problem in Access, you must identify the basic elements of your data vs. the computable elements. Then, remember this simple concept. Your computer can perform literally hundreds of millions all the way up to billions of operations per second. It isn't that nasty to have it recompute stuff for you on-the-fly. However, if you store tons of computations in a table, you condemn yourself for having to wait for disk operations to recover that extended data record that you stored - and disks do things measured in tens of milliseconds. The bigger the record you stored (due to storing computed results), the longer it will take to recover your very long data records.

Which is faster? Waiting for a 15-30 msec disk op or waiting for a computer to perform an operation that takes NANOseconds? This is the mindset that you have to develop when considring Excel vs. Access. This is also why for larger datasets, Access kicks Excel's arse for performance. Fewer disk accesses (to recover shorter data records not clogged with stored data) means fewer wait-states.