Counting number of enrolments / passes by ethnicity or other describing variable (1 Viewer)

[Paine]

Hi all!

Before I went on holiday, I played around and managed to produce a cross-tab query which counted up the number of learners who passed a course, and broke this down by Special Educational Needs (e.g. Number of students with dyslexia who got 1 pass, 2 passes, 3 passes, etc). For some daft reason, I didn't save this on the test-run data and, you guessed it, got back from Cornwall and couldn't work out how I did it first time round! I now need to do this analysis for ethnic groups. If anyone can help refresh my memory, it would be gratefully appreciated!

So, here we go...

For the sake of simplicity, let's say we have 3 tables:

Young People demographic data (YP)
(YP).[yp ID]
(YP).[Name]
(YP).[DOB]
(YP).[Special Educational Needs]
(YP).[Ethnicity]

Courses (C)
(C).[course ID]
(C).[course name]

Enrolments (E)
(E).[enrolment ID]
(E).[yp ID]
(E).[course ID]
(E).[start date]
(E).[end date]
(E).[outcome] (this includes the option 'passed')

Now, from here I've created a query called 'Achievements' which includes:

(E).[enrolment ID]
(E).[yp ID]
(YP).[Name]
(YP).[Special Educational Needs]
(YP).[Ethnicity]
(E).[course ID]
(C).[course name]
(E).[outcome] - criteria set as 'passed'

From here, I want to have a cross-tab that has the column heading: e.g. 1, 2, 5, 6, 7, 10, 12, 23, (Number of passes) and row heading: White British, Black African, etc (the groups specified within [Ethnicity]). The table should be able to tell me how many White British learners got 2 passes, 4 passes, etc, compared with the other ethnic groups.

I KNOW this is possible because I managed to make Access do this with dummy data before I went on holiday. But I can't for the life of me remember how I did it!!! I've not had much luck searching around the internet.

Any help would be wonderful.

Thanks in anticipation



Steve

 

[Paine]

OK, let's keep this simple. Does anyone know how I would go about counting the number of passes that each learner has? From there I should be able to do more complicated things.

I've tried adding a SumofPasses: Count((E).[Outcome]) column to the query above but this gives me an answer of 1 each time - obvious because there is only 1 pass per record.

What I want to know is how many passes per learner. Do I need another query which sources data from this one?

 

[jdraw]

Here is format of a basic query (untested), you can adjust as needed.

Select
E.[yp ID]
,Count(E.outcome ) As Passes
FROM E
Group By E.[yp ID]
Having outcome ="passed"

 

[Paine]

Thanks Jdraw,I've not used SQL before, I've been using the queries - how would this be structured in a query? (But I shall have a play around and let you know if I suss it out!)

 

[Paine]

Thanks Jdraw, it was so simple!! I've just been overcomplicating things. Now I know how this is done it is going to really help, thank you very much.

 

[Paine]

Right, I've made a lot of progress - perhaps too much because I've started being overambitious again!

I've created a query, called Enrolment Analysis, which has:

[YP ID]
[Name]
[Surname]
[DOB]
[Special Needs]
[Ethnicity]
[Enrolments] (counts [enrolment ID])
[Passes] (result from another query which counts passes)
[Fails] (result from yet another query which counts fails)
[Results Pending] (result from a further query which counts pending results)
[Kicked off course] (yup another query)
[Left school] (and another...)
[Length of stay] (and yet another)

It gives me a wonderful summary document which should be a good source of data for future cross tabs, excel exports, forms, etc, and it wouldn't have been possible without you all (thanks!)

Now, I tried to be clever and add another column which went...

Pass rate: ([Passes]\([Passes]+[Fails])*100)

BUT I have null values in both columns. So, I thought I would be even more clever and add new columns called Passes_noNull and Fails_noNull which I could use to fo the Pass Rate and these could read...

Passes_noNull: IIF([Passes]="",0,[Passes])
and the same again for Fails.

Of course, this gives me a circular reference and Access doesn't like this! Is there another way I could tackle null here, using the query?

As you can probably tell, I'm still quite a novice at this - but I'm proud of what we've got together so far!

Thanks for baring with me...



Steve

 

[jdraw]

You can use NZ function

NZ( fldname, value if null)

so NZ (IntX,0) will return Intx's value if it is not null, and 0 if it is null

 

[Paine]

Thanks, that's a really handy formula - I've gone through other queries and used it to improve results. I'm particularly pleased because combining it with Date() in a sum has allowed me to work out the total number of days that a young person has been with us.

But, it does seem to have a side-effect. In the query above I have tried adding two extra columns: [Total] and [Pass Rate]. My hope was that Total would be [Passes]+[Fails], and then Pass Rate would be [Passes]\[Total].

Well, [Total] seems to be turning up quite unusual results. 7 passes plus 0 fails = 70. 11 passes plus 1 fail = 111. It looks like the formula is just appending the numbers rather than adding them in the standard numerical way!

Firstly, does anyone know why this is?

Secondly, is it actually possible to do what I am trying to here - work out an achievement rate - or am I going to cause myself grief because it'll be generating circular references?

Thanks, and sorry for the bother!