• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Comparing multiple vales and finding the largest one in access fields

Hi Experts

I have a query containing values and the (15) columns are labelled A - O representing multichoice exam question options.  When an exam is run, a varying quantity of candidates pick options so I end up with something resembling the following:

QuestionID  A  B  C  D  E......O
12345           2  10 2  5  7.....

What I want to do is find the largest value, in this case 10, then compare it to the correct answer value, this is in a different query and return a statement "Possible Wrong Answer (PWA)"

I have attached an exported version of query into excel and highlighted items to show how the comparison should work.
qryOptionCands.xlsx
0
andymacf
Asked:
andymacf
  • 6
  • 6
1 Solution
 
John EastonDirectorCommented:
I think what you are looking for is:   =IF(MAX(C2:Q2)=S2,"OK","PWA")
0
 
Rey Obrero (Capricorn1)Commented:
in ACCESS, you will need a UDF to do this

place this codes in a regular module

function getMax(paramarray sArr())
dim j, xMax
xMax=sarr(0)
for j=1 to ubound(sArr)
    if sArr(j) > xMax then
        xMax=sarr(j)
    end if
next
getMax=xMax
end function

to use in a query

select * , getMax([col1],[col2],,,,[coln])
from tableName
0
 
andymacfAuthor Commented:
JEaston, thanks for your response but I am keen to do this in Access.

Capricorn1, thank you.  In the select statement, can I pull my values from a crosstab query or does it have to be a table?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
preferably use a table, crosstab queries have variable columns..
0
 
andymacfAuthor Commented:
Hi Capricorn1

Unfortunately, using a table is a bit more difficult, as this forms part of another calculation, however, my crosstab query will always have 15 columns and 245 rows.  I hope, therefore, that their is a workable solution.

Andy
0
 
Rey Obrero (Capricorn1)Commented:
can you post the SQL of the crosstab query
0
 
andymacfAuthor Commented:
Yep, here you go

TRANSFORM Sum(qryOptionStatsCands.Value) AS SumOfValue
SELECT qryOptionStatsCands.sample_nm, qryOptionStatsCands.accession_label
FROM qryOptionStatsCands
GROUP BY qryOptionStatsCands.sample_nm, qryOptionStatsCands.accession_label
PIVOT qryOptionStatsCands.label;

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
the problem with the crosstab query is the name of the fields to use in your getMax() function

create a table using your crosstab query, something like this

select yourCrosstabQueryName.* into MyTable
from  yourCrosstabQueryName


then use the table "Mytable" in the query

select * , getMax([col1],[col2],,,,[coln])
from mytable
0
 
andymacfAuthor Commented:
Thanks Capricorn1

Ok, i have created the table and now have my two linking fields followed by the 15 columns of data.  I have tried a new query using your suggestion but am getting an error.

Here is my SQL:
SELECT tblOptionsMean.sample_nm, tblOptionsMean.accession_label, tblOptionsMean.A, tblOptionsMean.B, tblOptionsMean.C, tblOptionsMean.D, tblOptionsMean.E, tblOptionsMean.F, tblOptionsMean.G, tblOptionsMean.H, tblOptionsMean.I, tblOptionsMean.J, tblOptionsMean.K, tblOptionsMean.L, tblOptionsMean.M, tblOptionsMean.N, tblOptionsMean.O
FROM tblOptionsMean;

Open in new window


I have tried adding this to the query without success:
GetMax: Max([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L],[M],[N],[O])

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
that is not the way i wrote the query..it should be


MaxValue:GetMax([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L],[M],[N],[O])

Open in new window



the whole query

Select *, GetMax([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L],[M],[N],[O]) as MaxValue
FROM tblOptionsMean

Open in new window

0
 
andymacfAuthor Commented:
Hi Capricorn1

I have pasted the SQL as described above and receive the following error:

Undefined function 'GetMax' in expression

Also, will this give me one value for each row of data?

Andy
0
 
Rey Obrero (Capricorn1)Commented:
<Undefined function 'GetMax' in expression>

read my post at http:#a39208205 


<Also, will this give me one value for each row of data?>

yes
0
 
andymacfAuthor Commented:
Thanks for solving this for me and apologies for missing the link back to the previous post.  This works fine

Thanks again
Andy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now