Difficult criteria?

[ScottishFencer]

I have a DB which contains a lst of 'standards' which need to be assessed to see if a minimum level has been met. each standard is numbered 1.1 1.2 1.3 and so on. They also have an 'award level': Below, Achieves, Exceeds and Outstanding. I am attempting to find a way that allows me to ask the following question:

If 1.1 -> 1.6 is Exceeds or Outstanding then it is pass, else fail.

There are other sections 3,4,5,6,7 and 8 all number as above.

In order for the assessment to pass there must be a pass in every section (1,3,4,5,6,7 and 8).

I've got a crude filter which gives me these criteria but now I am stuck for a way to evaluate the crude result set. Any ideas?

crude filter:

SELECT tblCADOffice.txtMem, Max(tblSADOffice.txtAssID) AS MaxOftxtAssID, tblSADOffice.txtCode, tblSADOffice.memTrans
FROM tblSADOffice INNER JOIN tblCADOffice ON tblSADOffice.txtAssID = tblCADOffice.txtID
WHERE (((tblCADOffice.txtMem) Like "es*") AND ((tblSADOffice.txtScheme)="vsf") AND ((tblSADOffice.txtCode) Like "1.*" Or (tblSADOffice.txtCode) Like "3.*" Or (tblSADOffice.txtCode) Like "4.*" Or (tblSADOffice.txtCode) Like "5.*" Or (tblSADOffice.txtCode) Like "6.*" Or (tblSADOffice.txtCode) Like "7.*" Or (tblSADOffice.txtCode) Like "8.*") AND ((tblSADOffice.memTrans) Not Like "*below*" And (tblSADOffice.memTrans) Not Like "*achieves*" And (tblSADOffice.memTrans) Not Like "*not applicable*"))
GROUP BY tblCADOffice.txtMem, tblSADOffice.txtCode, tblSADOffice.memTrans
ORDER BY tblCADOffice.txtMem, Max(tblSADOffice.txtAssID), tblSADOffice.txtCode;

 

[emozley]

Would it make the table ridiculously large and complicated if you had a column for each section eg 1.1, 1.2, 1.3, 3.1, 3.2, 3.3, 3.4, 4.1, 4.2 etc then put a value in each column representing wheter it was Below, Achieves, Exceeds or Outstanding?

You could then code a page to look at each column and if it was eithe exceeds or outstanding then it would look at the next column and if it got all the way to the end it would mark a yes/no field called Pass as TRUE otherwise it would be false.

You could then run a simple query to find all records where Pass was set to true.

 

[ScottishFencer]

Oops, I didn't explain myself very clearly. There already is a results column. This contains Exceeds, Outstanding etc however the problem is this:

If you even have one achieves in the 6 standards then it is a no go. What the 'crude filter' does is remove the achieves and lower ... The situation is that you may well have Exceeds or better in every category bar one achieves and you should be dropped in the list. The problem I am having is getting my head round the logice required to say if there is an achieves 9or below) on this list of criteria then it is a fail otherwise pass.

 

[lespaul]

could you display your table?

tableName
Field1 Field2 Field3
data data data
data data data
data data data
data data data

and your expected results from that sample?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ScottishFencer]

Ok,

The data looks this:
[tt]
txtMem MaxOftxtAssID txtCode memTrans
000001 47937 1.1 "1.1b Exceeds Entry Standard - Reservation was handled in a friendly and efficient manner"
000001 47937 1.2 "1.1b Exceeds Entry Standard - A very friendly welcome with my order taken in the lounge"
000001 47937 1.3 "1.1a Outstanding - Friendly and informative with ample time to compose my self before being asked for my order"
000001 47937 1.4 "1.1b Exceeds Entry Standard - Smart in corporate uniform"
000001 47937 1.5 "1.1b Exceeds Entry Standard - Very good serving skills and attentive to the guests needs throughout the meal without being intrusive."
[/tt]
As you can see this entry only has 5 entries for the first group of standards, this is because [in this case] the 6th is an 'achieves or lower".

In this case the assessed establishment would fail. If standard 6 was an "Exceeds or Ouststanding" they would pass. Additionally they CANNOT have an achieves or lower standard, in any other category (strict assessments - I know).

 

[lespaul]

so you need to find all establishments where the total count for each txtAssID meets your requirements?

Is this how the raw data is stored? Is there some other field to check besides this memTrans field for the Achieves/Exceeds/Outstanding?