UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating A 2 Plus/minus Range From A Given Number, Access 2013    
 
   
Lagerca
post Nov 13 2019, 07:11 PM
Post#1



Posts: 305
Joined: 14-April 06



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.
Go to the top of the page
 
MadPiet
post Nov 13 2019, 07:33 PM
Post#2



Posts: 3,364
Joined: 27-February 09



ABS(difference)<=2?
Go to the top of the page
 
Lagerca
post Nov 14 2019, 07:34 PM
Post#3



Posts: 305
Joined: 14-April 06



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.
Go to the top of the page
 
Lagerca
post Yesterday, 03:31 PM
Post#4



Posts: 305
Joined: 14-April 06



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,


Go to the top of the page
 
ADezii
post Yesterday, 03:55 PM
Post#5



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Sample Data (whole Numbers for weight):
    PersonWInNoWOutNo
    Person 1140142
    Person 2173172
    Person 3137141
    Person 4190192
    Person 5187184
    Person 6220225
    Person 7242241
    Person 8183179
    Person 9200203
    Person 10166164
    Person 11194213
    Person 12215216
  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:
    PersonWInNoWOutNoPoints
    Person 11401425
    Person 21731725
    Person 3137141
    Person 41901925
    Person 5187184
    Person 6220225
    Person 72422415
    Person 8183179
    Person 9200203
    Person 101661645
    Person 11194213
    Person 122152165

Go to the top of the page
 
Lagerca
post Today, 09:44 AM
Post#6



Posts: 305
Joined: 14-April 06



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.
Go to the top of the page
 
ADezii
post Today, 09:56 AM
Post#7



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


See if the following works for you.
Attached File(s)
Attached File  Weight___Revised.zip ( 21.82K )Number of downloads: 0
 
Go to the top of the page
 
Lagerca
post Today, 10:18 AM
Post#8



Posts: 305
Joined: 14-April 06



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

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 11:57 PM