[X]My Assistant

[Lagerca]

Good evening. I am trying to come up with a simple formula to create a Range from a given number.

Individuals begin our Holiday Season program with a Weigh In (lbs) field [WInNo]. At end of the 6 week program, the individual records his/her Weight (lbs) field [WOutNo]; I then calculate the difference as: DiffWOutWIn: [WOutNo]-[WInNo]

So, if I weigh in at 170 lbs and weigh out at 165 lbs; my result = -5 lbs, I lost pounds, no problem here.

Our goal though for this program is for the individuals to maintain their weight within a "plus or minus 2 lbs" during this 45 day period; this is not an exercise event to lose weight; but too maintain. If they maintain their weight "+/- 2 lbs", they are awarded 5 pts at the end of the program; there are other activities within the program to gain other points. If they gain more than 2 lbs or lose more than 2 lbs; no points are awarded. However; we also measure Body Fat % and there are points allotted here for reducing BF%

Back to the Q: What would be a good formula to use with this goal in mind to award them the 5 pts if they fall into a particular range of their "Weigh In" event, using the three fields? Thank You kindly... Curtis.

[MadPiet]

ABS(difference)<=2?

[Lagerca]

Good evening MadPiet. Thank you; your suggestion worked; just a brief summary how it worked for others.

1. I used the formula “PM2lbs: Abs([DiffWOutWin]) <=2” which gave me only a 0 or a -1 for each record. To grant the 5 points for those maintaining the Plus/Minus 2lbs, I used “PM2lbsPts: IIf([PM2lbs] = -1, 5, 0)”

2. Then for those maintaining the Plus/Minu 2% Body Fat goal, I used your same suggestion. In building the formula, the initial difference between Beg and End BF%; I used “DiffWOPWIP: [WOPerc]-WIPerc]” which gave me either a plus or minus value. To isolate the Plus/Minus 2% Body Fat records, I used “PM2PerBF: Abs([DiffWOPWIP] <=2” and then to award the 5 points for those maintaining that goal, “PM2BFPts: IIf([PM2PerBF] = -1,5,0)”. Finally awarding 10 points for those folks that had a greater than 2% in Body Fat loss; I used “G2PerBFPts: IIf([DiffWOPWIP] <-2, 10,0)”

Though I learned something. I had Body Fat % figures in my table formatted as Field Size: Single; Format as Percent, Decimal Places as 0. When I ran the formula above “PM2PerBF: Abs([DiffWOPWIP] <=2” the results for all records was a -1; no distinction to isolate the records. So, I changed the format in the table to a “Long Integer”; figuring I can display % format in my reports rather than the tables.

Thank you much, MadPiet for your suggestion. Curtis.

[Lagerca]

Good afternoon MadPiet. As a follow-up: The “Abs” formula works fine when I have two values: [WInNo] and [WOutNo], (No is a Number). I have though encountered an issue in my design where the individual did not Weigh In [WInNo] in the beginning of our program or may not Weigh Out [WOutNo] at the end. Five points are awarded [PM2lbsPts] if they remain within the plus/minus 2lbs of the Weigh In. Other points are awarded for other events in our program.

To isolate those records where either the Weigh In/Out have no values, I created [WIOND], no data. I have trouble though using the “Abs” formula for those records that have both values. I hope this is clear! Please see the SQL below.... thank you kindly. Curtis.

SELECT tblTeamInfo.TeamID, tblTeamInfo.TeamName, tblMemberInfo.IndID, tblMemberInfo.NameLF, tblMemberInfo.WInNo,

tblMemberInfo.WOutNo,

IIf([WInNo]=0 Or [WOutNo]=0,1,0) AS WIOND,

[WOutNo]-[WInNo] AS DiffWOutWIn,

Abs([DiffWOutWIn])<=2 AS PM2lbs,

IIf([PM2lbs]=-1,5,0) AS PM2lbsPts,

[ADezii]

1. Sample Data (whole Numbers for weight):
   

   Person	WInNo	WOutNo
   Person 1	140	142
   Person 2	173	172
   Person 3	137	141
   Person 4	190	192
   Person 5	187	184
   Person 6	220	225
   Person 7	242	241
   Person 8	183	179
   Person 9	200	203
   Person 10	166	164
   Person 11	194	213
   Person 12	215	216

2. Query Definition:
   
   SQL
   SELECT tblData.Person, tblData.WInNo, tblData.WOutNo, IIf(Abs([WInNo]-[WOutNo])<=2,5,Null) AS Points
   FROM tblData;
3. Query OUTPUT:
   

   Person	WInNo	WOutNo	Points
   Person 1	140	142	5
   Person 2	173	172	5
   Person 3	137	141
   Person 4	190	192	5
   Person 5	187	184
   Person 6	220	225
   Person 7	242	241	5
   Person 8	183	179
   Person 9	200	203
   Person 10	166	164	5
   Person 11	194	213
   Person 12	215	216	5

[Lagerca]

Good morning. Thank you... Almost! Your coding does work when we have both values for [WInNo] and [WOutNo]. It also works if we have one of the two values missing; 0 points awarded. However, if both fields have 0 values in each of them, we need to award them 0 points. Currently, when both values are missing, we still come up with 5 pts being awarded. Curtis.

[ADezii]

See if the following works for you.

Attached File(s)

 Weight___Revised.zip ( 21.82K )Number of downloads: 1

 

[Lagerca]

Good morning again. Since I am currently in my office, I can't download zip files; I will need to await my home; I will try that... However, I did find a temporary solution: using your abs code for [WIOPts]. Then using the isolated records [WIOND], if they are true, give those records 0 points, otherwise use the [WIOPts] points for [WtPts]. This seems to work.... Curtis.

SELECT tblTeamInfo.TeamID, tblTeamInfo.TeamName, tblMemberInfo.IndID, tblMemberInfo.NameLF, tblMemberInfo.WInNo, tblMemberInfo.WOutNo, IIf(Abs([WInNo]-[WOutNo])<=2,5,0) AS WIOPts, IIf([WInNo]=0 Or [WOutNo]=0,1,0) AS WIOND, IIf([WIOND]=1,0,[WIOPts]) AS WtPts

[Lagerca]

Yes, thank you ADezii. I looked at the zipped file and your coding: Points: IIf(Abs([WInNo]-[WOutNo])<=2,5,0) works, even if both values are empty, that is, there are no 0's. In establishing the design of the db, 0 is the default. If the default of data entries is 0, your formula "will grant" 5 points. However, if the data entry is empty, no 0's, then yes the coding works. Thank You. Curtis.