Use Select Case to Consolidate Switch Function

[P5C768]

I need to consolidate 5 fields stored in a table into 29 different categories (eventually to be used in a cross-tab query to generate counts of each category). I'm limited in using switch and/ or iif statements because of the number of nested statements I would need.



Is there a way to do this using by creating a function and using select case? I'm not sure how to reference multiple fields in a table. Thanks!

Here is an example of what I was origionally thinking of:

test: Switch([ORG Change]="Change" And [Class Change]
="Change" And [Range Maximum]="Down" And [Salary Change]
="Down","Demotion to Another Deartment with Pay Decrease",
[ORG Change]="Change" And [Class Change]="Change" And 
[Range Maximum]="Down" And [Salary Change]="No Change",
"Demotion to Another Deartment with No Pay Change",
[ORG Change]="Change" And [Class Change]="Change" And 
[Range Maximum]="Down" And [Salary Change]="Up",
"Demotion to Another Deartment with Pay Increase",
[ORG Change]="Change" And [Class Change]="Change" And 
[Range Maximum]="No Change" And [Salary Change]="Down",
"Transfer to Another Department with Pay Decrease",
[ORG Change]="Change" And [Class Change]="Change" And 
[Range Maximum]="No Change" And [Salary Change]=
"No Change","Transfer to Another Department with No Pay Change",
[ORG Change]="Change" And [Class Change]="Change" And 
[Range Maximum]="No Change" And [Salary Change]="Up",
"Transfer to Another Department with Pay Increase")

[pbaldy]

Sure; you'd have the function accept your 4 values (if I counted right) as input parameters, and output the text. Another option would be a table with 5 fields, the 4 parameter fields plus the output. Then you could get the value with a lookup or join.

[P5C768]

How do you set inidividual table values as inputs into a funtion? So if I'm understanding you right I could then just run a cross-tab query and count the occurence of each value in the table? I have approx. 40k records, would I need to store the values the function creates in a table?

[pbaldy]

Well, you already have the values in the query, so you'd just pass them. In design view of the query it would look like:

test: YourFunctionName([ORG Change], [Class Change], [Range Maximum], [Salary Change])

The function would use those values and If/Then or Select/Case to evaluate the inputs and return the appropriate string. The table I had in mind would look like:

[ORG Change]  [Class Change]  [Range Maximum]  [Salary Change]   ReturnValue
"Change" "Change" "Down" "Down"       "Demotion to Another Deartment with Pay Decrease"
"Change" "Change" "Down" "No Change"  "Demotion to Another Deartment with No Pay Change"

With all the potential combinations listed. Theoretically you could then join that table on the 4 fields and get the resulting string.