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

 

[bat17]

I would use the Switch function in a Query for this.

Peter

 

[Norie]

What data type is the step?

 

[Pushkinwow]

the data type is numeric/currency

and I am looking into the switch function - will see what pans out!

 

[Pushkinwow]

bat 17: I looked at thedescription of the switch function.

it seems to return one value after going through all of the expressions. i need something that will look at each value and then classify it accordingly.

that's why i figured if statements would be the best. I can just run all the data through a series of if statements, assign variables to what i want to count, and after all the data has been looked it I will have totals for my variables which I can then display in the report or query.

lol - any nice simple way to do it without coding would be nice though!

 

[bat17]

Swith works with matched pairs. it will give the first "true" result, use something like this to get your classification.

class: Switch([Salary]<1000,"Below 1",[SALARY]<3001,"grade 1",[SALARY]<4000,"Between 1 and 2",[SALARY]<6001,"grade 2,[SALARY]>7000,"Too Much")


HTH

Peter

 

[Norie]

Why not create a new column in the original table called say StepGroup?

Then create update queries that set it according to the step value?

Is the step actually determined by the salary?

 

[Abulafia]

the data type is numeric/currency

Well which? It can't be both Numeric and Currency - these are different data types.

I agree with the Switch() function.

If however, you feel it looks too long in the SQL then you can create a function that does the same thing using the SELECT CASE structure and using that in the SQL.

 

[Pushkinwow]

Just checked and the data is just a number (represents a currency but just a number).

I will have to play around with the switch function. it seems to be what i want but I am not too familiar with writing sql (although I am starting to get better with a friendly book and help from you guys).

Norie: the step is independent of the salary. ideally what i need is to set up parameters of the steps and then be able to classify each number from a table into your variable "groupstep".

 

[Norie]

Any chance of posting a little sample data?