Compare value to upper and lower boundaries

[CarolynD]

Hopefully I can explain this well enough - I have -

tbl1
Comp_ID
Component
GoalUpper
GoalLower
IsScored [y/n]
PointValue

tbl2
Emp_ID
Comp_ID
Value

What I want to do (in a report) is check to see if the tbl2.Value is between tbl1.GoalUpper and tbl1.GoalLower and if so - do something [either display "Yes" - for met Goal and/or add the PointValue to the Emp_IDs running total].

Suggestions?

Thanks in advance for your help !

Carolyn

 

[pbaldy]

I imagine this could be done directly on a report with some dlookup formulas to get the upper/lower numbers. I think it may be easier to do it in a query though. It would be easier with a sample db to play with, if you can attach one. I assume it would be by Comp_ID; does the "IsScored" field impact this?

 

[CarolynD]

Hi -

Thanks, attached is a sample DB ... the system itself is quite large and includes a bunch of other stuff (like Client IDs, etc.) but I won't bore ya with all that.

Yes, it is by Comp_ID - the IsScored (yes/no) needs to be checked before adding the point to the employees running total. Some of the Comp_IDs are scored and ee's are awarded bonus' depending on the total score; other Comp_IDs are not scored but are measured for tracking and reporting purposes.

Thanks again for any help you can offer ...

Carolyn

ps - not sure if this attachment is going to work; please let me know if it's not there if there's another way I can get it to you ...

 

[pbaldy]

The attachment didn't make it. For this site, it has to be zipped and under 100k. Make sure you do a compact/repair before zipping. If that doesn't work, I can give you an email address to send it to.

 

[CarolynD]

Hi -

I did zip it (using WinZip) and it's 23K ... not sure why this isn't working ... but I'll try again.

Thanks in advance for your help!

 

[CarolynD]

Hi -

I got it ... I did do it in a query Paul - thanks for the direction : for those that also struggle with this in the future, here's what I did.

In a query; matching on COMP_ID:

Success: IIf(([Value]>=[GoalLower] And [Value]<=[GoalUpper]),1,0)

Point: IIf(IIf(([Value]>=[GoalLower] And [Value]<=[GoalUpper]),1,0)=1,[PointValue],0)

The Success gives me a 1 or 0 for Yes/No and the Point gives me the point value.

Have a great day ~!

Carolyn