Help manipulating data please

[browniebodrum]

I used to know what I was doing with Access but I'm really rusty and can't
find a way to solve this problem, so any help would be much aprreciated...

I've simplified things for this posting, so let's say I've got 10 records
and 5 fields (in practice there are a lot more but the principles will be the
same). Each record represents one person's answers to five questions. The
'answer' in each of the five fields has a numeric value between -42 and +42.

What I need to do is to find out how many people put their lowest answer for
question 1, then how many people put their lowest answer for question 2 and
so on. If a person has put the same lowest, numeric answer for more than one
question, their response needs to be included in the count-up for each of
those questions .

I realise I could sort the answers for each person manually, make a note of
which question or questions they put their lowest value for, then count them
up. But I am sure there are lots of bright people out there that can explain
how to automate the process. Hope so, anyway...! ;-)

 

[browniebodrum]

Thanks very much for this, I think it will do the trick. PS I especially
appreciated the clear instructions in real English and no typos... ;-)

 

[hmadyson]

Ok, I am going to take a lot of liberties with this one but this is what I
think is the fastest way to do it.

First, your data will be better off if it is in a large array table with the
keys of student id and question #. So each person has as many records in that
table as the questions that they have answered. The layout of the new table
is:

studentID long
question integer
answer integer

Now the following sql will get you the min answer per studentid (query named
minanswer)

SELECT personanswers.personID, Min(personanswers.answer) AS MinAns
FROM personanswers
GROUP BY personanswers.personID;

the following will tell you which question had this min answer (named
minquestion)

SELECT personanswers.personID, personanswers.question
FROM minanswer INNER JOIN personanswers ON (minanswer.MinAns =
personanswers.answer) AND (minanswer.personID = personanswers.personID);

and the following query will get you a count of how many were answered per
question number (although you will not get a list of all questions, just the
ones with at least 1 marked as lowest)

SELECT minquestion.question, Count(minquestion.personID) AS CountOfpersonID
FROM minquestion
GROUP BY minquestion.question;

Please let me know if this suited your needs, or if I can help you more.