Expression Builder Issues (1 Viewer)

[gary.newport]

I have a field called criteria_id that can have a series of values but fall into one of three groups: Px, Mz and Dy where x, y and z are numbers from 1 to a given value.

I would like to create a query that counts the number of "Px", "Mz" and "Dy" - regardless of value (so, if I have P1, P2, P3 and P4 I want the count to return 4).

I would then, ideally in the same expression, compare the results of this calculation against three other fields in the query (no_pass, no_merit and no_dist) and return PASS, MERIT, DISTINCTION depending upon the result of the comparison, or BELOW PASS if there is no acceptable comparison.

So, the system says that no_pass=8, no_merit=3 and no_dist=2. If the result of the number of Px = 8 but the others are below the required value then PASS, if Px=8 and Mz=3 then MERIT and Px=8, Mz=3, Dy=2 then DISTINCTION.

They MUST have Px=8 before Mz can be considered. They must have Mz=3 before Dy can be considered. If Px<8 then BELOW PASS.

Am I asking too much from an expression? Should I be coding this instead?

 

[plog]

My brain can parse this in the abstract. Can you post some sample data? Give me 2 sets: starting sample data before you run the query, then what data you expect from this query based on that initial sample starting data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 34, 5/16/2006
Larry, 88, 9/7/2008
Sally, 22, 6/8/2009

 

[Simon_MT]

One does wonder if Px=9 then Merit and PX=10 then Distinction.

Simon

 

[Keith Tedbury]

IIf([criteria_id] Like "P*",1,0) This would return a 1 for a record where the criteria_id started with P and 0 if it didn't. If you then summed this you would get a count for all the records.

The * after the P means it will look for something that starts with but P doesn't matter what is after it. If you use "*P" It would look for things that ended in P and "*P*" would return anything with a p in it.

I would count the Number of P, M ,D in one query and then do the comparison in a second query that calls the first.

Hope this helps to get you started.