Solved

Comparing multiple vales and finding the largest one in access fields

Posted on 2013-05-30
13
195 Views
Last Modified: 2013-06-03
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
Comment
Question by:andymacf
  • 6
  • 6
13 Comments
 
LVL 10

Expert Comment

by:JEaston
ID: 39208190
I think what you are looking for is:   =IF(MAX(C2:Q2)=S2,"OK","PWA")
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39208205
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
 
LVL 7

Author Comment

by:andymacf
ID: 39208245
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39208264
preferably use a table, crosstab queries have variable columns..
0
 
LVL 7

Author Comment

by:andymacf
ID: 39210087
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39210609
can you post the SQL of the crosstab query
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 7

Author Comment

by:andymacf
ID: 39210888
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39210989
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
 
LVL 7

Author Comment

by:andymacf
ID: 39211219
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39211261
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
 
LVL 7

Author Comment

by:andymacf
ID: 39215521
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39216083
<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
 
LVL 7

Author Closing Comment

by:andymacf
ID: 39216115
Thanks for solving this for me and apologies for missing the link back to the previous post.  This works fine

Thanks again
Andy
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now