Solved

Comparing multiple vales and finding the largest one in access fields

Posted on 2013-05-30
13
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39210609
can you post the SQL of the crosstab query
0
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

733 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