Calculating the difference between grades?

[Paine]

Dear all,

I have created a database system used to manage learner records. I am trying to measure progression (i.e. number of grades that a student goes up or down during their time studying with us) through a query - but I've hit a wall and could do with a fresh pair of eyes!

Background - the database is huge, our turnover is approx 60 students per month. The relevant tables here are:

Receptions and Leavers
Fields include: [Lit at Reception], [Num at Reception], [Lit at Leaving], [Num at Leaving]

Func Grades
Fields are: [Autonumber_ID], [Func Skill Grade], [Grade_Value]

Func Grades is a reference table used as a lookup for the fields mentioned in Receptions and Leavers. The values in Func grades are:

ID__Grade Grade_Value
1___L3________7
2___L2________6
3___L1________5
4___E3________4
5___E2________3
6___E1________2
7___Pre-E______1
8___Refused____Null
9___None______Null

I had hoped I could create a query which, along with all the necessary ID and date fields, could have a column which subtracted the Grade_Value of whatever was in [Lit at Reception] from whatever is in [Lit at Leaving] to say, e.g. 1. This way I can then compare the average period of study against the number of levels the learner progressed in their studies, and we can do some sophisticated analysis to see how factors such as ethnicity, free school meals, shoe size, etc, affect academic attainment.

My first attempt was:
[Receptions and Leavers].[Lit at Reception]
[Receptions and Leavers].[Num at Reception]
[Receptions and Leavers].[Lit at Leaving]
[Receptions and Leavers].[Num at Leaving]
Lit_Progression: [Lit at Leaving]-[Lit at Reception]
Num_Progression: [Num at Leaving]-[Num at Reception]

Of course, it's never this simple is it!? Obvious errors because the query doesn't recognise the value of each grade from the Lit / Num fields. But, If I add the Grade_Value column to the query then this also messes things up.

Is there a way I can make the query calculate the difference between the two sets of grades using the value of the grade from the reference table?

Any help much appreciated!

 

[CBrighton]

Firstly I'd replace null with 0, performing sums with null is asking for trouble.

Secondly, you likely want 2 instances of the reference table open, one linked to [Lit At Reception] and another tied to [Lit At Leaving].

You can then include the two Grade_Value fields from the two reference tables and use them to perform sums. I'd advise giving these 2 fields names too so you can easily tell which one applies to the initial grade and which one applies to the end grade, I would personally move the fields next to the field they refer to as well so that a quick glance at the data will tell me if each field is showing the correct grade value.

 

[Paine]

Thanks, works perfectly.

With regard to using null... the problem of using 0 is that this gives misleading information. For example, if a learner leaves at L2 (value=6) but we don't have a reception grade or they refused to be assessed at the start of their course (value=0), then their progression would come through as bizarrely high (6 grades!).

Is there a way we can tell the query that all null values should be treated as the same as the leaver value - or even better that if a null value is found then 0 is assumed as the result of the sum?

Thanks again for this - much appreciated.

 

[CBrighton]

This should do it:

Iif(IsNull([Lit At Reception]),[Lit At Leaving],[Lit At Reception]

Should return the leaving grade if the reception grade was null and the reception grade if not.

Obviously adjust the field names as required.

:edit:

Alternatively, to perform the sum only when not null try something like this:

If(Not IsNull([Lit At Reception]),**INSERT CURRENT CODE**,0)

 

[Paine]

Thanks. I haven't removed the null values yet, the current set-up seems to do what I want it to, but perhaps I need to because...

The query processes this information perfectly (yay!) - but the sub form that it is linked to this within a form is now locked-out / unupdatable. Is there a way to overcome this? We use a subform called 'Leavers' to input leaving dates, reasons for leaving, levels at leaving, etc. There is a calculation within the query that already works out the Length of Stay so I am quite surprised that adding new calculations for levels appears to have jammed the whole thing up?

The join between [Receptions and Leavers] and [Func Skill Grade] is set to include ALL records from Receptions and Leaves and only those records from the reference table where joined fields are equal.

I want to be able to input the exit grade into the subform and then this form will tell me the levels of progression that a particular learner has achieved.

Or do I need to tackle the Null issue to solve this?

Apologies, this has stumped me a bit - I think you need to be able to visualise things in 4D sometimes!

 

[CBrighton]

Should be unrelated to null.

Calc's in queries & queries being updatable is something I always have issues with!

Will the grade difference be displayed on that form or is it soley for a report or a different form? If it's not needed on that form then I would just create a second query which is how your current one was before you added the grade calculation.

If it needs to be displayed then you could still do the above 2 queries but set the textbox with the difference in a subform which is bound to the query which has the calc (as the calculated field would never be edited manually the lock shouldn't be an issue on a subform).

 

[Paine]

Thank you for all your help!

We basically have a form called Young People with key details at the top. Under this are cross-tab controls with a subform in each, relating to different aspects of the young person's details (e.g. exams sat, reception details, leaver details, etc). In the subform tab relating to leaver details I have details about the learner leaving, linked to a query, including Length of Stay, levels at leaving, etc. I was hoping to have the progression appear here.

But I've bitten the bullet and just added another subform underneath this one which shows the calculation for progression (made in a seperate query) - doesn't look pretty but it works perfectly! Lord knows why it doesn't work when it's combined with the query in the first place, one day I'll understand Access...

Thank you for all your help on this, much appreciated

 

[CBrighton]

No problem.

With tweaking of the settings there will be no evidence it's a subform at all.

Take a look at the subform control's properties. Altering the border style & special effect properties should allow you to hide any visual effects showing that it's a subform (and you can obviously delete the label that adding a subform creates for you).