SWITCH expression using values from 2 fields

[bodhi23]

Hi all -
I've been poking around for something that will help me formulate an expression for this, and I've found some things that are very close, but not close enough for direct application...

I expect someone will tell me if I'm going about the the completely wrong way, I am looking for the simplest solution...

Here goes: We've got a tutor training program with 3 levels. If the tutor attends 10 training sessions and meets with students for 25 hours per level, they move up one. So 10 Level 1 training sessions plus 25 or more hours means Level 1, 10 Level 2 training sessions plus 50 or more hours means level 2, 10 Level 3 training session plus 75 or more hours means level 3.

I think that a SWITCH statement in either a query or report control will do what I want, but the syntax eludes me. (I was originally thinking nested IIF statements, but some searching here gave me the indication that it could be less complicated with a SWITCH - but see below for the truth of that!)

I have a query already that counts up the training sessions grouped by level. How do I apply a grouping within a SWITCH statement? Is it even possible?

Here's my first test:

= SWITCH([rptSUBCountTutorIndivSessions].Report![CountOfTotal Time] =BETWEEN 25 AND 49 AND [rptSUBCountTutorTraining].Report![Level] =1 AND [rptSUBCountTutorTraining].Report![CountOfAttended] =10, "Level 1",_[rptSUBCountTutorIndivSessions].Report![CountOfTotal Time] =BETWEEN 50 AND 74 AND [rptSUBCountTutorTraining].Report![Level] =2 AND [rptSUBCountTutorTraining].Report![CountOfAttended] =10, "Level 2",_[rptSUBCountTutorIndivSessions].Report![CountOfTotal Time] >75 AND [rptSUBCountTutorTraining].Report![Level] =3 AND [rptSUBCountTutorTraining].Report![CountOfAttended] =10, "Level 3",_TRUE, "No Level")

But I get a syntax error telling me I must have put a comma in the wrong place...

My ultimate goal is to generate a report that shows how many face to face hours a tutor has accumulated, how many training sessions completed in each level, and then display a calculated indicator telling what level the tutor has reached.

I currently have a report that shows the first two bits of this using a base report of the tutors by name with two sub-reports (generated by query). I'm in the process of puzzling how to add the level indicator to the report... If someone can think of a better way to accomplish the task, I'm always open to suggestions!

Thanks in advance...

 

[FoFa]

Well I would write a function in VBA you basically would some ID (tutors ID?) and it would return the level number.
The function would go rollup the data for you, say via an ADO recordset and a query.
Usually the fastest way. You can call the functions from your eports base query, or the report directly (but i would go with the base query).

 

[bodhi23]

Um, I'm sorry - I forgot to mention VBA is a foreign language to me... I've been working with Access for only a few years, and I know just about enough to get myself into trouble with it if I'm not careful... Most of the more advanced coding is a bit beyond my current level of expertise.

We are using the tutor's ID number as a common key between the tables.

Can I ask you to give me that explanation again in something a little closer to layman's terms?

I need to write something that will look at the number of training sessions per level, plus the number of face to face hours and tell me Level 1, Level 2, Level 3 or No Level... (i.e.: print it on the report)

 

[FoFa]

I would create a query that summarizes the training sessions per their max level ),1,2 or 3 with 0 being no level. The query would return Tutor ID and the max level number and the summarized sessions for that level.
I would create a second query that would summarize the face-to-face hours for a tutor, it would return the tutor ID and the summarized hours.

Now it depends more on you here, what just makes more sense to you. You could either create a third query that matched those two together, and created one row per tutor of their max level, training sessions for that level, total face-to-face hours and of course the tutor ID, and a column with an IIF that calculates the LEVEL from the other fields.

Then all you have to do is join this query on Tutor ID to whatever other query/ies you write and you always have it. This might be the most easiest way for you at this point in time.m Just make sure you outer join this query to your main query in case there are no enteries for either sessions or face-to-face hours (new tutor). Outer join is where you click the join line and tell it to return all records from your MAIN table and ANY that match from this new query.

Is that clear as mud?

 

[raskew]

Hi -

Putting your switch() into a readable format, changing the between 25 and 49 to >= 25 (don't specify an upper limit--what happens if the number is 50, you'll end up with 'No Level') and it seems to work.

Same with the [rptSUBCountTutorTraining].Report![CountOfAttended] =10.
Once again, if this total = 11, you'll end up with "No Level". Change to >=10.

Here's a working example from the debug window:

'************************************************
'x = rptSUBCountTutorIndivSessions].Report![CountOfTotal Time]
'y = rptSUBCountTutorTraining].Report!Level
'z = rptSUBCountTutorTraining].Report![CountOfAttended]


x = 50
y = 1
z = 10

? switch(x>= 25 and y = 1 and z >= 10, "Level 1", _
       x>= 50 and y = 2 and z >= 10, "Level 2", _
       x>=75 and y = 3 and z >= 10, "Level 3", _
       True, "No Level")

Level 1
'************************************************

HTH - Bob

 

[bodhi23]

..as mud, yes.

I've got a query already that counts the face to face hours - those aren't attached to levels - we just know that 75 hours means the tutor can move to level 3 if they've got the requisite training...

If I create a 2nd and maybe 3rd query as you're suggesting, how would I indicate in the levels query that I want just the max level? Would that be done in the criteria using a SELECT statement of some sort? As I was searching earlier I saw some thread about golf scores that gave an example of a SELECT statement using TOP...

I get the gist of what you're saying, my issue is specifically constructing the expression that will make Access tell me "Level X" when the hours and sessions are met. I can build queries all day that will pull the base data...

I thought IIF at first, but it really doesn't work well for more than 2 options. I've got 3 (or 4 if you count no level). From what I've been reading, you can put TRUE, "return value" at the end of a SWITCH statement to catch errors and anything else that doesn't evaluate in the first 3 parts of the expression - which is where the "No Level" idea came from, but it's not as important to me as the rest - obviously if no level is indicated, they haven't reached the right numbers yet.

A tutor can accumulate more face to face hours than are needed before they finish the associated training, so the expression needs to take into account a range of hours, rather than specifically 25 - it wouldn't return someone who had 26 hours if I used the exact number. I guess I could use =/>25 to get that...

If it makes a difference, this back and forth is actually helpful in and of itself...

 

[bodhi23]

Ok - how 'bout this?

I already have queries that pull the numbers I'm concerned with. So if I put those two queries into another query with my list of tutors table and make an outer join on the id fields, I get a list of all of my tutors with the number of hours and the number of training sessions. All well and good (makes the subreports obsolete, yes? I could just build the base report from this...)

So I tried just one part of an expression, since I have one tutor in my test database with 113 hours and all of her training complete:

Expr1: IIf([CountOfTotal Time]>=75 And [Level]=3 And [CountOfAttended]=10,3,0)

I wanted it to say "Level 3", but apparently, since it's a calculation dealing with numbers, text was not making it happy. But I got an error. So then I removed the word Level (I can always label it in the report) and the quote marks, but I still get an error. I think I'm getting closer - just needs tweaking...

Edit: the space in that Total Time fieldname was not of my making, but I should probably go in and fix that name - if it's not causing a problem in this, it sure might somewhere else!

 

[bodhi23]

Raskew,
Thanks - that's much closer to what I'm looking for. I wouldn't use a >= for the training sessions, as each level contains only 10, and you can't move up a level until you attend all 10 sessions - that one will always be "=". But I did figure out the >= for the number of hours just before I saw your post! That was a forehead smacker...

So I worked up the switch expression using your syntax, and put it into my joined query, but it still gave me a syntax error...

weird, eh? Do I need to mention I'm using Access 07? It does weird things - maybe that makes a difference...
This is the syntax it gives in the help files:

Switch(expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )

That is an underscore at the end of each line, correct? Does it require a space before and after? The cursor keeps going to the first underscore in my expression when I get the syntax error...

So, looking at the Access 07 syntax, and removing the underscores allows the final part of the expression to evaluate properly, but the first three parts return errors... I think I'm getting closer...

 

[bodhi23]

Here's the last thing I'm doing on it tonight:

Expr1: Switch([CountOfTotal Time]>=25 And [Level]=1 And [CountOfAttended]=10,"Level 1",[CountOfTotal Time]>=50 And [Level]=2 And [CountOfAttended]=10,"Level 2",[CountOfTotal Time]>=75 And [Level]=3 And [CountOfAttended]=10,"Level 3",True,"No Level")

This returns an error on the first 3 parts, but evaluates everything else according to the final part just fine...

It's just about time to leave the office, Eastern Standard Time... Surely this will all look better in the morning.

 

[raskew]

Substituting x, y and z for your field names:

x = [CountOfTotal Time]
y = [Level]
z = [CountOfAttended]

It looks like this. Made no other changes than
the x,y,z business
Switch(x>=25 And y=1 And z=10,"Level 1", _
x>=50 And y=2 And z=10,"Level 2", _
x>=75 And y=3 And z =10,"Level 3", _
True,"No Level")

Testing it in the immediate (debug) window

x = 26   
y = 1    
z = 10 

? Switch(x>=25 And y=1 And z=10,"Level 1", _
x>=50 And y=2 And z=10,"Level 2", _
x>=75 And y=3 And z =10,"Level 3", _
True,"No Level")
Level 1

Tested it in both A97 and A2003 (don't have A2007, thank God) and it
seems to work as advertised.

This returns an error on the first 3 parts, but evaluates everything else according to the final part just fine...

You lost me there. Please provide a little more detail.

Bob

 

[bodhi23]

A07 is a royal pain. There are some things that A03 did very well that seem not to work in A07 at all... (like, you can't export a report to Excel - could do it in 03, but not in 07 for some reason... )

This returns an error on the first 3 parts, but evaluates everything else according to the final part just fine...

What I mean is, anything that would evaluate as true in the first three parts of the expression comes up as an error in the datasheet view of the query.

So if a tutor's face to face hours equal the amount for level 1, and they have the requisite training in that level, it should return Level 1 in that column. But I get #Error instead. Same goes for anyone who meets criteria for levels 2 and 3. But anything that evaluates True at the last part, (TRUE, "No Level") populates with No Level.

The A07 syntax has the commas that separate each part of the expression inside the brackets of each set of values. I tried to use the bracket symbol around each part of the expression yesterday, but it gave me a syntax error. I'm going to try it this morning using additional parenthesis and see if that makes a difference. My table/field names appear in brackets, so I'm guessing that was the issue. I'll let you know how it goes...

 

[bodhi23]

Ok, yesterday was a busy day and I didn't have a chance to get back to this until now.

I have played around with brackets and parenthesis in the expression based on the syntax provided by Raskew - which was determined to be a functional expression.

When I put parenthesis around the expression part of the function (i.e.: (([CountOfTotalTime]>=25 And [Level]=1 And [CountOfAttended]=10),"Level 1",([CountOfTotalTime]>=50 And [Level]=2 And [CountOfAttended]=10),"Level 2",...etc.) the error message that comes up tells me I have a function with the wrong number of arguments.

I ran into this with another report in our conversion between Office 03 and Office 07. For whatever reason, Access 07 will not evaluate an expression if there are too many parts in it.

We have a report that calculates how many student hours our staff accumulate each month in specific service areas. In Access 03, that report could calculate the amount of hours from as many fields as we cared to include in the expression. Access 07 returned an error for any expression that was asked to sum more than 3 or 4 fields. We ended up having to split the expressions up to make sure that fewer than 4 fields were being summed at any one time (I'm not sure if I explained that clearly - but I'm not sure how else to say it...). So now I'm wondering if the SWITCH function in Access 07 is subject to the same type of limitation, in that it will only evaluate the expression where only one field is indicated...

So I tested it. I reduced the expression down to:
([CountOfTotalTime]>=25 ,"Level 1", True, "No Level")
and guess what? it returns the level.

So apparently, I can't evaluate three fields using the SWITCH function - I can only evaluate one. Unfortunately, we have to compare multiple fields to get the information... *sigh*
What a pain.

 

[bodhi23]

I figured out a work-around. And sent some feedback to Microsoft - as if it will do any good... damn limitations.

I had to split what I wanted, and since the expression became simpler, I could use 1 nested IIF statement and one standard IIF.

Here are the two statements I worked out:

LevelTraining: IIf([CountOfAttended]=10,"Training Complete","Incomplete")

CRLASessions: IIf([CountOfTotalTime] Between 25 And 49,"Level 1 Met",IIf([CountOfTotalTime] Between 50 And 74,"Level 2 Met",IIf([CountOfTotalTime]>=75,"Level 3 Met","Not Met")))

They worked perfectly.
Here's a sample of the output (sans report headers, since I was using a sample, most of the results weren't accurate...):

Thanks for all your help - I always learn something when I visit this site, there is always a wealth of information here...