Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Comparing multiple vales and finding the largest one in access fields

Posted on 2013-05-30
13
Medium Priority
?
201 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 11

Expert Comment

by:John Easton
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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