help finding mode and median

[Chris Barnett]

hi.

i'm a teacher and i have developed a database to collect data and print out
parents reports.

i have been asked to provide some statistical information also for teachers
based on the groups.

the fields for the table is as follows

Admin (a unique pupil number)
Subject (e.g. Maths, English etc)
Set (the specific group e.g. 9Ma1, 7En3
Attain (a number level from 2 to 8 although it is stored as a text
field)
Classwork (a grade from A-D based on effort)
Homework (A-D similar to classwork)
Behaviour (A is excellent to D poor)


The requirement is create a report that groups the records into Sets and
then provide the average Attain level. More difficult though is also to to
report the most common grade letter for each of Classwork, Homework &
Behaviour.

so
Adm Sub Set Att Cls Hwk Beh
101 Maths 9Ma1 4 A B A
102 Maths 9Ma1 6 B B A
103 Maths 9Ma1 5 C C B
104 Maths 9Ma1 5 B A A
105 Maths 9Ma2 3 D D D
106 Maths 9Ma2 5 C B C
107 Maths 9Ma2 3 C B D
108 Maths 9Ma2 5 B B B

would result in a report looking like

Maths
Set Att Cls Hwk Beh
9Ma1 5 B B A
9Ma2 4 C B D


i hope this makes sense and would be grateful for any help


thanks

Chris Barnett

 

[Larry Daugherty]

Hi Chris,

A motivated and effective teacher is as valuable to the country as a
soldier. Thank you for what you do. I have one daughter who is an
elementary school teacher. Another daughter is a senior marketing
manager for HP. She is getting a teaching endorsement to her masters
degree so she can leave industry (and all that filthy money) and go
teach kids.

I'll take a shot at your project. All comments and questions are
intended to help

You're not being specific. What kind(s) of help do you seek;
evaluation of your design, help to improve the design, tell you what
you are able to do with Access???

From your explanation and expressed design it appears that you're
entering summary data and you will summarize it to a higher level and
report the results.

What are your personal objectives in this project; in priority order.
near term and long term if they're different?

Achieve a credible design and get the report DONE.

Learn all I can about Access because it will be helpful to me and
others
and when I'm good enough I might do some work on the side ...

Other - specify

What is your current level of expertise in Access? I'm guessing
you're a novice. If that's so, be aware that Access has a long steep
learning curve. It's worse than Excel and Word combined. You have to
learn quite a bit before you can do much of anything useful.Another
thing is that more rigor is required in Access than in the other
application platforms that constitute MS Office. In Word and Excel
you can get away with cheap tricks and cutting corners. You can in
Access at the very lowest levels of development but end up with such a
mess that it becomes impossible to enhance further. If you know
without a moment's hesitation the formal definitions of "data
normalization" and "third normal form" then you are far beyond a
novice and you can design your data well enough to do anything with an
Access database. We can get to work right away.

On the other hand, if you don't know the definitions we can get to
work right. It's just that this way, I'll have to do all of the
lifting.

By the way, what Access books have you read and studied? If the
answer is "None" then I suggest you get your hands on "Running Access
[YourVersion} Step by Step" from Microsoft Press. Actually any
version from Access 2.0 onward, will do just fine. If there aren't
any handy locally you can get it new from MS, Amazon, Borders, Barnes
& Noble and a bunch of other places. If cost is an issue you can get
some very good (and trustworthy) deals from Barnes and Noble Used
Books. Look for the title and see if they offer Used. I believe
Amazon offers Used as well. If you can't find that one, keep an eye
out for an Access for Dummies book. They're pretty good.

It's expected that most threads in these newsgroups are of the nature
and scope of "Here's my specific problem, this is my schema (you've
done well there), please help me fix it". In that context all
correspondence is kept in the thread so that others can see the issues
and the suggested solutions. There is a great deal of resistance to
"taking it offline". However, I'm suggesting that we take this one
offline. Your issue appears broad and open ended even though your
data set is very small. You can easily decode my email address and
start the ball rolling Come back with your contact information and
the best time to call you. Well communicate mostly by email and
sometimes by phone. I haven't used Instant Messenger for years but if
you're familiar with it and like to use it, I can re-learn quickly.
Don't post your contact information into the newsgroup.

The cost for my participation can be steep: you have to remain
involved until the project is done and you have to be reasonably
responsive to my requests project information. That's it.

Otherwise, post back with answers to specific questions and we (I
and/or others) can address the issues one at a time.

HTH

 

[James A. Fortune]

hi.

i'm a teacher and i have developed a database to collect data and print out
parents reports.

i have been asked to provide some statistical information also for teachers
based on the groups.

the fields for the table is as follows

Admin (a unique pupil number)
Subject (e.g. Maths, English etc)
Set (the specific group e.g. 9Ma1, 7En3
Attain (a number level from 2 to 8 although it is stored as a text
field)
Classwork (a grade from A-D based on effort)
Homework (A-D similar to classwork)
Behaviour (A is excellent to D poor)


The requirement is create a report that groups the records into Sets and
then provide the average Attain level. More difficult though is also to to
report the most common grade letter for each of Classwork, Homework &
Behaviour.

so
Adm Sub Set Att Cls Hwk Beh
101 Maths 9Ma1 4 A B A
102 Maths 9Ma1 6 B B A
103 Maths 9Ma1 5 C C B
104 Maths 9Ma1 5 B A A
105 Maths 9Ma2 3 D D D
106 Maths 9Ma2 5 C B C
107 Maths 9Ma2 3 C B D
108 Maths 9Ma2 5 B B B

would result in a report looking like

Maths
Set Att Cls Hwk Beh
9Ma1 5 B B A
9Ma2 4 C B D


i hope this makes sense and would be grateful for any help


thanks

Chris Barnett

I created tblPupilSet as follows:

tblPupilSet
PSID Autonumber
Adm Long
Sub Text
Set Text
Att Text
Cls Text
Hwk Text
Beh Text

and put your sample data in.

Then I created some auxiliary queries:

qryAllSets:
SELECT DISTINCT [Set] FROM tblPupilSet;

qryCountBeh:
SELECT [Set], Beh, Count(Beh) AS CountOfBeh FROM tblPupilSet WHERE Beh
IS NOT NULL GROUP BY [Set], Beh;

qryCountCls:
SELECT [Set], Cls, Count(Cls) AS CountOfCls FROM tblPupilSet WHERE Cls
IS NOT NULL GROUP BY [Set], Cls;

qryCountHwk:
SELECT [Set], Hwk, Count(Hwk) AS CountOfHwk FROM tblPupilSet WHERE Hwk
IS NOT NULL GROUP BY [Set], Hwk;

qryForReport:
SELECT [Set], (SELECT First(Cls) FROM qryCountCls AS A WHERE
A.CountOfCls = (SELECT Max(B.CountOfCls) FROM qryCountCls AS B WHERE
B.Set = qryAllSets.Set) AND A.Set = qryAllSets.Set) AS ModeCls, (SELECT
First(Hwk) FROM qryCountHwk AS A WHERE A.CountOfHwk = (SELECT
Max(B.CountOfHwk) FROM qryCountHwk AS B WHERE B.Set = qryAllSets.Set)
AND A.Set = qryAllSets.Set) AS ModeHwk, (SELECT First(Beh) FROM
qryCountBeh AS A WHERE A.CountOfBeh= (SELECT Max(B.CountOfBeh) FROM
qryCountBeh AS B WHERE B.Set = qryAllSets.Set) AND A.Set =
qryAllSets.Set) AS ModeBeh FROM qryAllSets;

!qryForReport:
Set ModeCls ModeHwk ModeBeh
9Ma1 B B A
9Ma2 C B D

Note that in the case of a tie the query grabs the first maximum
frequency value. Note that 'SET' as a field name can potentially
confuse SQL. Also, if I recall correctly, 'Cls' is a function in some
kinds of Basic and can potentially confuse VBA. So I suggest renaming
those if they weren't simply abbreviations for posting. Change
tblPupilSet to whatever name you are using for your table in all
instances. I recommend more thorough testing before using this
technique since I only tested it on the sample data you provided. Post
back if you need more help.

James A. Fortune

Please do not send anything to my email address.

The humble improve. -- Wynton Marsalis

 

[Chris Barnett]

thanks for your help with this one sorry for taking so long to respond - i
haven't quite got it to work as i'd like (i actually have around 30000
seperate records to group into sets) however i have found an alternative way
of presenting the data (with your help) so thanks

I am actually struggling though about creating average attainment levels.
the levels should be normally a number from 1 to 8, however there is the odd
one which is a letter code and so i have to store it as a text field. I'm
sure it's quite simple and i have done it before but don't seem to be able
to manage it with this but how do you get the value from the text field and
calculate the average for each sets.?

thanks for any further help with this

cheers
Chris Barnett

hi.

i'm a teacher and i have developed a database to collect data and print
out parents reports.

i have been asked to provide some statistical information also for
teachers based on the groups.

the fields for the table is as follows

Admin (a unique pupil number)
Subject (e.g. Maths, English etc)
Set (the specific group e.g. 9Ma1, 7En3
Attain (a number level from 2 to 8 although it is stored as a text
field)
Classwork (a grade from A-D based on effort)
Homework (A-D similar to classwork)
Behaviour (A is excellent to D poor)


The requirement is create a report that groups the records into Sets and
then provide the average Attain level. More difficult though is also to
to report the most common grade letter for each of Classwork, Homework &
Behaviour.

so
Adm Sub Set Att Cls Hwk Beh
101 Maths 9Ma1 4 A B A
102 Maths 9Ma1 6 B B A
103 Maths 9Ma1 5 C C B
104 Maths 9Ma1 5 B A A
105 Maths 9Ma2 3 D D D
106 Maths 9Ma2 5 C B C
107 Maths 9Ma2 3 C B D
108 Maths 9Ma2 5 B B B

would result in a report looking like

Maths
Set Att Cls Hwk Beh
9Ma1 5 B B A
9Ma2 4 C B D


i hope this makes sense and would be grateful for any help


thanks

Chris Barnett

I created tblPupilSet as follows:

tblPupilSet
PSID Autonumber
Adm Long
Sub Text
Set Text
Att Text
Cls Text
Hwk Text
Beh Text

and put your sample data in.

Then I created some auxiliary queries:

qryAllSets:
SELECT DISTINCT [Set] FROM tblPupilSet;

qryCountBeh:
SELECT [Set], Beh, Count(Beh) AS CountOfBeh FROM tblPupilSet WHERE Beh IS
NOT NULL GROUP BY [Set], Beh;

qryCountCls:
SELECT [Set], Cls, Count(Cls) AS CountOfCls FROM tblPupilSet WHERE Cls IS
NOT NULL GROUP BY [Set], Cls;

qryCountHwk:
SELECT [Set], Hwk, Count(Hwk) AS CountOfHwk FROM tblPupilSet WHERE Hwk IS
NOT NULL GROUP BY [Set], Hwk;

qryForReport:
SELECT [Set], (SELECT First(Cls) FROM qryCountCls AS A WHERE A.CountOfCls
= (SELECT Max(B.CountOfCls) FROM qryCountCls AS B WHERE B.Set =
qryAllSets.Set) AND A.Set = qryAllSets.Set) AS ModeCls, (SELECT First(Hwk)
FROM qryCountHwk AS A WHERE A.CountOfHwk = (SELECT Max(B.CountOfHwk) FROM
qryCountHwk AS B WHERE B.Set = qryAllSets.Set) AND A.Set = qryAllSets.Set)
AS ModeHwk, (SELECT First(Beh) FROM qryCountBeh AS A WHERE A.CountOfBeh=
(SELECT Max(B.CountOfBeh) FROM qryCountBeh AS B WHERE B.Set =
qryAllSets.Set) AND A.Set = qryAllSets.Set) AS ModeBeh FROM qryAllSets;

!qryForReport:
Set ModeCls ModeHwk ModeBeh
9Ma1 B B A
9Ma2 C B D

Note that in the case of a tie the query grabs the first maximum frequency
value. Note that 'SET' as a field name can potentially confuse SQL.
Also, if I recall correctly, 'Cls' is a function in some kinds of Basic
and can potentially confuse VBA. So I suggest renaming those if they
weren't simply abbreviations for posting. Change tblPupilSet to whatever
name you are using for your table in all instances. I recommend more
thorough testing before using this technique since I only tested it on the
sample data you provided. Post back if you need more help.

James A. Fortune

Please do not send anything to my email address.

The humble improve. -- Wynton Marsalis

 

[James A. Fortune]

thanks for your help with this one sorry for taking so long to respond - i
haven't quite got it to work as i'd like (i actually have around 30000
seperate records to group into sets) however i have found an alternative way
of presenting the data (with your help) so thanks

I am actually struggling though about creating average attainment levels.
the levels should be normally a number from 1 to 8, however there is the odd
one which is a letter code and so i have to store it as a text field. I'm
sure it's quite simple and i have done it before but don't seem to be able
to manage it with this but how do you get the value from the text field and
calculate the average for each sets.?

thanks for any further help with this

cheers
Chris Barnett

hi.

i'm a teacher and i have developed a database to collect data and print
out parents reports.

i have been asked to provide some statistical information also for
teachers based on the groups.

the fields for the table is as follows

Admin (a unique pupil number)
Subject (e.g. Maths, English etc)
Set (the specific group e.g. 9Ma1, 7En3
Attain (a number level from 2 to 8 although it is stored as a text
field)
Classwork (a grade from A-D based on effort)
Homework (A-D similar to classwork)
Behaviour (A is excellent to D poor)


The requirement is create a report that groups the records into Sets and
then provide the average Attain level. More difficult though is also to
to report the most common grade letter for each of Classwork, Homework &
Behaviour.

so
Adm Sub Set Att Cls Hwk Beh
101 Maths 9Ma1 4 A B A
102 Maths 9Ma1 6 B B A
103 Maths 9Ma1 5 C C B
104 Maths 9Ma1 5 B A A
105 Maths 9Ma2 3 D D D
106 Maths 9Ma2 5 C B C
107 Maths 9Ma2 3 C B D
108 Maths 9Ma2 5 B B B

would result in a report looking like

Maths
Set Att Cls Hwk Beh
9Ma1 5 B B A
9Ma2 4 C B D


i hope this makes sense and would be grateful for any help


thanks

Chris Barnett

I created tblPupilSet as follows:

tblPupilSet
PSID Autonumber
Adm Long
Sub Text
Set Text
Att Text
Cls Text
Hwk Text
Beh Text

and put your sample data in.

Then I created some auxiliary queries:

qryAllSets:
SELECT DISTINCT [Set] FROM tblPupilSet;

qryCountBeh:
SELECT [Set], Beh, Count(Beh) AS CountOfBeh FROM tblPupilSet WHERE Beh IS
NOT NULL GROUP BY [Set], Beh;

qryCountCls:
SELECT [Set], Cls, Count(Cls) AS CountOfCls FROM tblPupilSet WHERE Cls IS
NOT NULL GROUP BY [Set], Cls;

qryCountHwk:
SELECT [Set], Hwk, Count(Hwk) AS CountOfHwk FROM tblPupilSet WHERE Hwk IS
NOT NULL GROUP BY [Set], Hwk;

qryForReport:
SELECT [Set], (SELECT First(Cls) FROM qryCountCls AS A WHERE A.CountOfCls
= (SELECT Max(B.CountOfCls) FROM qryCountCls AS B WHERE B.Set =
qryAllSets.Set) AND A.Set = qryAllSets.Set) AS ModeCls, (SELECT First(Hwk)
FROM qryCountHwk AS A WHERE A.CountOfHwk = (SELECT Max(B.CountOfHwk) FROM
qryCountHwk AS B WHERE B.Set = qryAllSets.Set) AND A.Set = qryAllSets.Set)
AS ModeHwk, (SELECT First(Beh) FROM qryCountBeh AS A WHERE A.CountOfBeh=
(SELECT Max(B.CountOfBeh) FROM qryCountBeh AS B WHERE B.Set =
qryAllSets.Set) AND A.Set = qryAllSets.Set) AS ModeBeh FROM qryAllSets;

!qryForReport:
Set ModeCls ModeHwk ModeBeh
9Ma1 B B A
9Ma2 C B D

Note that in the case of a tie the query grabs the first maximum frequency
value. Note that 'SET' as a field name can potentially confuse SQL.
Also, if I recall correctly, 'Cls' is a function in some kinds of Basic
and can potentially confuse VBA. So I suggest renaming those if they
weren't simply abbreviations for posting. Change tblPupilSet to whatever
name you are using for your table in all instances. I recommend more
thorough testing before using this technique since I only tested it on the
sample data you provided. Post back if you need more help.

James A. Fortune

Please do not send anything to my email address.

The humble improve. -- Wynton Marsalis

Normally, you would use Val(Attain) to convert "3" to 3, etc. for use in
expressions. Perhaps you could use something like IIf(Attain='X', 1,
Val(Attain)) in place of [Attain] if 'Attain IS NOT NULL' is specified
in the WHERE clause so that the Null values don't bring down the
average. Decide whether to use the mean or the median. Avg() can be
used if the mean will suffice.

James A. Fortune