Solved

Comparing multiple vales and finding the largest one in access fields

Posted on 2013-05-30
13
194 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

759 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

19 Experts available now in Live!

Get 1:1 Help Now