Rank results

[Tanya]

Hi, I have a make table query which totals a students results in a multiple
intelligence quiz the student is required to rate between 1-5 for each
question, with 8 categories and 8 questions in each. Thus results of this
query gives me something like this:
Verbal/Linguistic Logical/Mathematical Naturalist
20 38 31

Now... of the eight categories I need to find out which is the students
strengths, in other words I need to rank these totals from highest to lowest.

I have no idea where to begin with this problem, any ideas are appreciated.

Kind Regards
Tanya

 

[scubadiver]

Do I get the suspicion that each question has its own field?

 

[KARL DEWEY]

First use a union query to change from spreadsheet to a database format.
SELECT Student, "Verbal/Linguistic" AS Skill, [Verbal/Linguistic] AS Score
FROM YourTable
UNION ALL SELECT Student, "Logical/Mathematical" AS Skill,
[Logical/Mathematical] AS Score
FROM YourTable
UNION ALL SELECT Student, "Naturalist" AS Skill, [Naturalist] AS Score
FROM YourTable;

SELECT Q.[Student], Q.Skill, Q.[Score], (SELECT COUNT(*) FROM YourUnionQuery
Q1
WHERE Q1.[Student] = Q.[Student]
AND Q1.[Skill] = Q.[Skill]
ANDQ1.[Score] >= Q.[Score])+1 AS Rank
FROM YourUnionQuery AS Q
ORDER BY Q.[Student], Q.Skill, Q.[Score];

 

[Tanya]

Thank you for your quick response Karl, I have not come across a union query
before, making this more interesting for me.

I tried what you have suggested and the first sql worked well, however the
second gave me an error: This operation is not allowed in subqueries
Perhaps I typed something incorrect?

---------Query Named 'MI Union Query'

SELECT Student, "Verbal/Linguistic" AS Skill, [Verbal/Linguistic] AS Score
FROM MI_Totals
UNION ALL SELECT Student, "Logical/Mathematical" AS Skill,
[Logical/Mathematical] AS Score FROM MI_Totals
UNION ALL SELECT Student, "Naturalistl" AS Skill, [Naturalist] AS Score FROM
MI_Totals;


--------Query Named 'Rank Query'

SELECT Q.[Student], Q.[Score], Q.[Score], (SELECT COUNT(*) FROM MI Union
Query
Q1
WHERE Q1.[Student] = Q.[Student]
AND Q1.[Skill] = Q.[Skill]
AND Q1.[Score>=Q.[Score])+1 AS Rank
FROM MI Union Query AS Q
ORDER BY Q.[Student], Q.Skill, Q.[Score];


Kind Regards
Tanya

 

[scubadiver]

If you give each question its own record in one table then you can achieve
what Karl is suggesting much more easily.

Give it some thought, especially if the database is it to be used repeatedly.

 

[Tanya]

Hi scubadiver

My original table has 64+ fields which allows me to ask 8 questions for each
multiple intelligence i.e. 8 x 8

I then ran a make table query which totalled the scores for each set of 8
questions, narrowing my data down to 8+ fields [including student name]

Back to my problem now... I need to be able to rank these multiple
intelligence scores to identify strengths and weaknesses in students.

Once I am able to rank the students scores for each MI I intend to create a
chart of only the top 3 or 5 since Access won't allow for all 8 MI [multiple
intelligences] scores

If there is an easier way I would love to know...

Thanks for your contribution.

Cheers
Tanya

 

[scubadiver]

Hello,

There is no 'easy' way with a database only the 'right' way. Even if you try
something intuitive it is likely to be the wrong way.

If you have all your responses in one table with every respondent and
question then it will become much easier.

I *think* what you need are four tables

Respondents
Resp_ID (PK)

Responses
Q_ID (FK)
Resp_ID (FK)
Responses

Questions
S_ID (FK)
Q_ID (PK)

Sections
S_ID (PK)

What I would do is have respondent in the main form and responses in the
subform. It gets a bit tricky at this point because what you can do is click
on the subform and then append all 64 questions for each respondent. To be
fair I can't remember how to do this though I did manage it for another
database.

Once you append the questions you can enter responses without having to keep
using the mouse to manually move from one cell to the next.

Hi scubadiver

My original table has 64+ fields which allows me to ask 8 questions for each
multiple intelligence i.e. 8 x 8

I then ran a make table query which totalled the scores for each set of 8
questions, narrowing my data down to 8+ fields [including student name]

Back to my problem now... I need to be able to rank these multiple
intelligence scores to identify strengths and weaknesses in students.

Once I am able to rank the students scores for each MI I intend to create a
chart of only the top 3 or 5 since Access won't allow for all 8 MI [multiple
intelligences] scores

If there is an easier way I would love to know...

Thanks for your contribution.

Cheers
Tanya

If you give each question its own record in one table then you can achieve
what Karl is suggesting much more easily.

Give it some thought, especially if the database is it to be used repeatedly.

 

[Tanya]

Hi scubadiver

What do you mean by append, are you referring to the relationship through
use of foreign keys? i.e. in this case respondent is my student table and you
are saying in responses table to have a FK studentID?

cheers
Tanya

 

[scubadiver]

What I mean is that you can append the questions to the responses table by
using an append query. Instead of writing out every question manually you can
list all 64 questions in one go simply by clicking on the subform.

I have found another database in which I did this (but it is at home). I can
give it a go and send it to you if you give me your email address (but don't
make it so recognisable that it will get picked up by spam or phishing
software)

 

[Tanya]

Thank you for the offer scubadiver.

My email address is (e-mail address removed)