IF(AND( Statement in table validation

[Desstro]

Hello,

I have a table that I would like to have a calculated field that returns a category so to speak based on the data in another field.
In excel I was able to acheive this with the following formula. How do I write this in an Access table in the expression builder?

Excel Formula...
=IF(G2>=75,"Cool Down",IF(AND(G2<75,G2>=65),"Warm Ups and Rests",IF(AND(G2<65,G2>=55),"Work It Girl",IF(AND(G2<55,G2>=45),"Hill Climb","Kill Me"))))

The G2 in this formula would be equal to the field "BPM" in the table and the numbers in the formula represent the data that would be in various records in that field.



I recognize it is a little different in the expression builder - IIF for example, I am having difficulty determining how to buil the IIF(AND( portion... Please help.

[CJ_London]

the equivalent in access would be

=IIF([G2]>=75,"Cool Down",IIF([G2]<75 AND [G2]>=65,"Warm Ups and Rests",IIF(AND([G2]<65 AND [G2]>=55,"Work It Girl",IIF([G2]<55 AND [G2]>=45,"Hill Climb","Kill Me"))))

where [G2] is the name of your field - BPM

However this should not be in an access table, but either in a query or as the controlsource to an unbound control on a form or report. If used in a query there are other functions you can use instead such as Switch or Partition

Access is not a bigger excel - it is completely different. Excel combines data and presentation (i.e. calculations) in a single view and the data is typically stored is 'wide and short'. Access stores data in tables and presents through queries which are then used in forms and reports. Data is stored 'narrow and tall'

Access works by using the rules of normalisation - in its simplest terms, stores data only once, Excel is about as far away from normalisation as you can get. Consider your list of gym member activities - how many times do you repeat the same gym members name or other details? In Access you have a table to list the members details (once), another table to list the exercises and yet another the measurements. You can even have a table of categories and instead of using the iif statement you would lookup the category for any given BPM. Benefit here is if you decided to change the category, or the BPM range it applies to, you would not have to go into the code to change it, you would change it once in the table.

[Desstro]

I get all that and I appreciate your help with the conversion from excel to Access. So I guess here is my new question. I have a table that has the BPM field. There are already 300 plus records in the table. I get using the form to have this data write to the table as I intend but, how do I get this data to matriculate to all of the 300+ records that are already in the table without having to go record by record in the table itself with manual data entry? I thought I would be able to do that by using the expression builder but apparently not?

[CJ_London]

use a query and put your iif statement in that.

[Desstro]

So... just to clarify for other readers of the thread, who may be looking for an answer to a similar question as I had, like I thought, it was possible to do this in the table with the expression builder without the use of a form or a query.

Now when the end user enters data in the form, BPM being a required field, the table will automatically update another calculated field in the table. In the expression builder I typed the following: And thanks again Ajax for the reminder on how Access works.

=IIF([Average Cadence On Down Beat Only]>=90,"90 Plus",IIF([Average Cadence On Down Beat Only]<90 AND [Average Cadence On Down Beat Only]>=80,"80 to 89",IIF([Average Cadence On Down Beat Only]<80 AND [Average Cadence On Down Beat Only]>=70,"70 to 79",IIF([Average Cadence On Down Beat Only]<70 AND [Average Cadence On Down Beat Only]>=60,"60 to 69",IIF([Average Cadence On Down Beat Only]<60 AND [Average Cadence On Down Beat Only]>=50,"50 to 59", IIF([Average Cadence On Down Beat Only]<50 AND [Average Cadence On Down Beat Only]>=40,"40 to 49","Less Than 40"))))))

[ssanfu]

Here are 2 more examples of your calculation using the SWITCH function:

=Switch([Average Cadence On Down Beat Only]>=90,"90 Plus",[Average Cadence On Down Beat Only]<90 And [Average Cadence On Down Beat Only]>=80,"80 to 89",[Average Cadence On Down Beat Only]<80 And [Average Cadence On Down Beat Only]>=70,"70 to 79",[Average Cadence On Down Beat Only]<70 And [Average Cadence On Down Beat Only]>=60,"60 to 69",[Average Cadence On Down Beat Only]<60 And [Average Cadence On Down Beat Only]>=50,"50 to 59",[Average Cadence On Down Beat Only]<50 And [Average Cadence On Down Beat Only]>=40,"40 to 49",[Average Cadence On Down Beat Only]<40,"Less Than 40")

=Switch([Average Cadence On Down Beat Only]>=90,"90 Plus",[Average Cadence On Down Beat Only] Between 80 And 89,"80 to 89",[Average Cadence On Down Beat Only] Between 70 And 79,"70 to 79",[Average Cadence On Down Beat Only] Between 60 And 69,"60 to 69",[Average Cadence On Down Beat Only] Between 50 And 59,"50 to 59",[Average Cadence On Down Beat Only] Between 40 And 49,"40 to 49",[Average Cadence On Down Beat Only]<40,"Less Than 40")

Here is your calculation using the IIF function:

=IIF([Average Cadence On Down Beat Only]>=90,"90 Plus",
IIF([Average Cadence On Down Beat Only]<90 AND [Average Cadence On Down Beat Only]>=80,"80 to 89",
IIF([Average Cadence On Down Beat Only]<80 AND [Average Cadence On Down Beat Only]>=70,"70 to 79",
IIF([Average Cadence On Down Beat Only]<70 AND [Average Cadence On Down Beat Only]>=60,"60 to 69",
IIF([Average Cadence On Down Beat Only]<60 AND [Average Cadence On Down Beat Only]>=50,"50 to 59", 
IIF([Average Cadence On Down Beat Only]<50 AND [Average Cadence On Down Beat Only]>=40,"40 to 49",
"Less Than 40"))))))

The first SWITCH function

=Switch([Average Cadence On Down Beat Only]>=90,"90 Plus",
[Average Cadence On Down Beat Only]<90 And [Average Cadence On Down Beat Only]>=80,"80 to 89",
[Average Cadence On Down Beat Only]<80 And [Average Cadence On Down Beat Only]>=70,"70 to 79",
[Average Cadence On Down Beat Only]<70 And [Average Cadence On Down Beat Only]>=60,"60 to 69",
[Average Cadence On Down Beat Only]<60 And [Average Cadence On Down Beat Only]>=50,"50 to 59",
[Average Cadence On Down Beat Only]<50 And [Average Cadence On Down Beat Only]>=40,"40 to 49",
[Average Cadence On Down Beat Only]<40,"Less Than 40")

The 2nd (best) SWITCH function using the BETWEEN keyword:

=Switch([Average Cadence On Down Beat Only]>=90,"90 Plus",
[Average Cadence On Down Beat Only] Between 80 And 89,"80 to 89",
[Average Cadence On Down Beat Only] Between 70 And 79,"70 to 79",
[Average Cadence On Down Beat Only] Between 60 And 69,"60 to 69",
[Average Cadence On Down Beat Only] Between 50 And 59,"50 to 59",
[Average Cadence On Down Beat Only] Between 40 And 49,"40 to 49",
[Average Cadence On Down Beat Only]<40,"Less Than 40")

Which is easiest to read and understand?



BTW, "Average Cadence On Down Beat Only" is a horrendous field name; it is too long and it has SPACES in it.
Should NEVER use spaces in object names.

How about "AvgCadenceDownBeatOnly"?
This is too short, but could be as simple as "ACDBO". Or maybe "AvgCadenceDB"? (much better)
Only the programmer/developer should ever see the field names, so it really doesn't matter what the name is (to a point).
Your field name involves a LOT of typing... and greater chance of typos.....



My $0.02...........

[CJ_London]

you can simplify the IIF function

=IIF([Average Cadence On Down Beat Only]>=90,"90 Plus",
IIF([Average Cadence On Down Beat Only]>=80,"80 to 89",
IIF([Average Cadence On Down Beat Only]>=70,"70 to 79",
IIF([Average Cadence On Down Beat Only]>=60,"60 to 69",
IIF([Average Cadence On Down Beat Only]>=50,"50 to 59",
IIF([Average Cadence On Down Beat Only]>=40,"40 to 49",
"Less Than 40"))))))

because the calculation will stop when the conditions are met - a value of 70 won't meet the first two conditions, but will meet the third.

same principle could be applied to the excel version as well.

With regards calculated fields, just because it is possible, doesn't make it a good idea. The functionality was only added to make access compatible with sharepoint which uses lists rather than tables. Lists work differently to tables - these links may prove useful for anyone wanting to use them

http://allenbrowne.com/casu-14.html
http://allenbrowne.com/ser-45.html

Things to be aware of in particular, they cannot be indexed (so performance will be slow for large datasets), you cannot upsize your db without removing them since they are not supported in other db's. it can only use fields within the record and a few other functions, such as the iif function.

[Desstro]

Ajax / ssanfu,

Thanks again for the advice and the examples. I appreciate it.

Just to make you guys feel better, this database is going to have about 400 records in one table with maybe a dozen fields. Ever. I understand the inability to be indexed but because of it's tiny amount of data I'm not to worried about performance.

It has been years since I messed around with Access. I was asked by a friend If I could help with a personal project to make life a little easier. While I agree that the switch function does appear a little cleaner I think any of the code if fairly easy to read. I have already added my code to the DB and for purposes of not doing things twice I'm just going to stick with it for now but, I will keep that in mind for any future builds.

As far as the naming conventions go on field names, I completely agree and had forgotten the unwritten rule. However, Like I said, tiny little database, never gonna upgrade it, one time build. Set it and forget it kind of situation here guys. You said it best though when you said.. ..."so it really doesn't matter what the name is".

This really is one of the best forums I belong to and I really appreciate you guys keeping us novices inline!