Any SQL experts in?

[Yugguy]

The missus has a query and I don't know SQL:


The Problem


Subject LG RLG CWAG Alert
English 1.5 2.2 ???????
Maths 1.41 2.1 ???????


I need a query to update the CWAG Alert field, this is a alert that either says Possible Concern or On Target or Excellent Progress depending on how well the student is doing.

The criteria for each are the following: -

PC – if LG is less than the (Minimum Progress x RLG)
OT – if LG is less than the (maximum progress x RLG)
EP – if LG is greater than the (Maximum Progress x RLG)

The main problem is that the minimum and maximum progress rates are different for each subject.

My part solution within an Access update query just using the in built expression builder.

IIf(Subject="Maths" And LG<"-0.2"*RLG,”Possible Concern”, IIf(Subject="Maths" And LG<"0.2"*RLG, ”On Target”, IIf(Subject="Maths" And LG>"0.2"*RLG, “Excellent Progress”, IIf(Subject="English" And LG<"0.1"*RLG, ”Possible Concern”, IIf(Subject="English" And LG<"0.3"*RLG, ”On Target”, IIf(Subject="English" And LG>"0.3"*RLG, “Excellent Progress”,))))))

So because I have 27 subjects and therefore need to put them into this expression all with different rates, then expression builder not only runs out of characters (limit 1024) but when fudge my way around that problem I then got the error that it was too complex.

I know SQL with Access is the answer but have never done SQL so and not sure where to start.

[PetrolTed]

At a quick glance I'd say you need the parameters for each subject in another table rather than hard coding them.

[tigger1]

Always try not to hard code parameters - much easier to update a table than a query in the long rung.

Is this something that has to run itself on a regular basis, or is she just wanting to run a report query occasionally to check?

I'm off to a meeting in a minute - but should be able to give you a proper answer before I leave for home - otherwise drop me an email, I'll answer over the weekend (might not be until Sunday though, sorry!)

[pdV6]

It should be easy enough to do once the database structure is organised sensibly.

Trouble is, you've not given all the information required to suggest a workable solution (lots of implied knowledge there)

[tigger1]

OK, I'ev got 10 mins...I'll try to put together what I think is a sensible structure for the data. I'll keep editing this as I get chance over the weekend - but I REALLY would suggest going with a (partly) normalised arrangement, which will make it easier to update things as necessary.

I would go for XX tables (XX TBC!): -

Table Pupils
(pupilid int, firstname nvarchar(20), middlenamenvarchar(20), surnamenvarchar(20), DOB...etc, IsActive int)
Only odd things there are using a unique id field (pupilid) and IsActive to show people who you still use. This allows you to add/ remove people easily, cope with name changes etc

Table Subjects
(SubjectID int, SubjectName nvarchar(40), MinProg decimal(5,2), MaxProg decimal(5,2))

Table PupilProgress
(PupilID, SubjectID, LG, RLG, CWAG)

Obviously the links are on PupilID / SubjectID

Right, I *think* from my understanding of your problem that this now makes it much simpler to create the query you need.

select P.firstname, P.surname, S.SubjectName, PP.CWAG
from pupils P
inner join subject S
on p.subjectid = S.subjectID
inner join pupilprogress PP
on S.pupilid = pp.pupilid
and S.subjectID = pp.subjectID
where p.isactive = 1 --i.e. is true, as opposed to false = 0


Edited to add: Bollards - the point is to calculate CWAG, which I've ommitted!

So, I'll add that in - but the structure is what you need to get right first anyway! There is a big flaw in your explanation in that I'm sure it will be possible for both states PC and OT to be 'true' - at least it appears so? I've got to head home - but this looks like it is easily finishable from this point with a little more info from the OP.

Even if you don't like normalised tables, they are the way forward (usually!) - as they make things quicker and easier to maintain. It also looks a bit sexier too, which is important if you're into this kind of thing. I'll try to check this post / email but it probably won't be until Sunday - so if somebody wants to take the baton and claim the glory, feel free, you're a simple calc and a case statement away from done!

Edited by tigger1 on Friday 28th September 18:50