good old if statements

[Pushkinwow]

So here is the problem.

I am dealing with salary data and people are supposed to be classified into steps 1-5. Unfortunately people are all over the place (below step 1, between step 2 and 3, above step 5 at multiple rates) so needless to say its a mess.

I need to produce a report with the total number of people in each step. That would be easy if people were where they are supposed to be but as I outlined above they are all over the place. I figured that with if statements you should be able to set perameters (either in the query or in the report) to return all the people below step 1, all the people at step 1, all the people in between step 1 and 2, etc.

The actual if statements should be easy to write but I wanted some direction on where to put them (where in the report or query, and how to assign a variable to it etc).

Any help would be appreciated.

Thank you

 

[Pushkinwow]

To give you an idea of what I am talking about here is some output from the query:

Pay Rate Step Pay Scale CountOfPay Rate
29.54 XX B83 1
34.631 01 B83 7
34.7 XX B83 1
36.049 02 B83 13
37.133 XX B83 1
37.477 03 B83 20
38.895 04 B83 20
39.185 XX B83 1
40.324 05 B83 11
41.628 XX B83 1
44.122 XX B83 2
72.153 XX B83 1
75.967 XX B83 1

Now if I just wanted to know how many people were off the payscale (XX) that would be easy. the query resultss for that follow:

Step CountOfStep
01 7
02 13
03 20
04 20
05 11
XX 9

However since the data is entered incorrectly in the first place - XX is only supposed to denote people who are overpaid, and nobody is supposed to be between the steps - I have a problem. So I need a report that will produce how many people are at step 1-5, overpaid, and the how many people fall between the steps.

I had originally thouht that some sort of if statement (or combination) would allow me to look at every dollar amount and determine is it lower then step 1, is it equal, is it between step 1 and 2 etc until it was found to be true, and then a value could be returned to some sort of variable which would displayed as a count for the each possibility.

I think I provided enough information but if not please let me know.

And regardless, everyone thank you very much for your help!!! It is definately appreciated.

 

[bat17]

No real need to use SQL for the Switch() function as you can build it in the query grid using the builder tool.

Peter

 

[Pushkinwow]

Sounds good - will work through it today.

 

[Norie]

In your sample data what exactly do you mean by XX?

What data type is the field step?

Is the step based on pay rate

e.g. if 34.631 < pay rate <36.049 then Step between 1 and 2

 

[Pushkinwow]

XX means that it is not in a step (either over step, under step, or between steps)

The field step is just text (enables XX and 05 to be entered)

The pay rate is based on the step. Theoretically people should only be paid at the step level, but over time people end up being paid between steps or over step (by this I mean above all of the steps)


e.g. if 34.631 < pay rate <36.049 then Step between 1 and 2 YES

 

[bat17]

can you post the step values?

Peter

 

[Pushkinwow]

Yup.

Step 1: 34.631
Step 2: 36.049
Step 3: 37.477
Step 4: 38.895
Step 5: 40.324