CODEWITHSUNDEEP
sqlvbadatabasems-accesscolumnsorting
Rod Parker
Rod Parker

Reputation: 11

MS Access. Record the position of the highest number in a bunch of columns

My apology in advance, I am new to asking for help but I have struggeled with this for a long time.

I have an MS Access table that stores lots of info in columns. I need to be able to record at what position the highest number appears in each column, in a new table or query. NB: The table has over 40 columns so individual query's with 'sort by max' does not work for me.

A simplified version of the table would took like this; Table1

Position Col1 Col2 Col3 Col4
1 0.1 0.5 0.8 0.3
2 0.5 0.7 0.1 0.5
3 0.7 0.6 0.2 0.7
4 0.2 0.1 0.5 0.8
5 0.3 0.8 0.4 0.2
6 0.6 0.3 0.3 0.4

I need a new table or query that tells me what position the highest number is? The resulting table would look like this. Where Col1's highest number is at Position 3, Col2's highest number is at Position 5, Col3's highest number is at Position 1 and so on.

Col1R Col2R Col3R Col4R
3 5 1 4

Any help or direction in VBA, SQL or Query would be greatly appreciated. (I am using MS Access 2016)

Thank you everyone for your prompt and excellent responses, I have now tried your solutions and at first they work fine however whenyou add more data they return incorrect results. I should have supplied more info to start but just wnated to keep it simple.

For some resaon I cannot edit the tables or add correctly new tables to show extended info.

please have a look a that the 2 images for new table info and the results, thank you allin advance. table 1 info enter image description here

Upvotes: 1

Views: 307

Answers (3)

Spencer Barnes
Spencer Barnes

Reputation: 2877

I'd do it with subqueries in the SELECT statement, as follows:

SELECT 
(SELECT T.Position From Table1 T Where T.Col1 = Agg.Max1) AS Pos1,
(SELECT T.Position From Table1 T Where T.Col2 = Agg.Max2) AS Pos2,
(SELECT T.Position From Table1 T Where T.Col3 = Agg.Max3) AS Pos3,
(SELECT T.Position From Table1 T Where T.Col4 = Agg.Max4) AS Pos4
FROM
(SELECT Max(T1.Col1) AS Max1, Max(T1.Col2) AS Max2, Max(T1.Col3) AS Max3, Max(T1.Col4) AS Max4
FROM Table1 T1) AS Agg

I don't think that's much more wordy that other solutions here, I find it easier to skim-read and understand, and it would certainly work quicker than the VBA. Even for 40 columns as you say, it's little more than a bit of copy-and-pasting to get the result.

Upvotes: 0

AIMIN PAN
AIMIN PAN

Reputation: 1675

Here is an interesting answer. Written in sql server, I am not familiar with access, but I think you can try the same way.

Assume you have less than 1000 rows.

select 
  max(convert(int, Col1 * 100) * 10000 + Position) % 10000 as Col1R,
  max(convert(int, Col2 * 100) * 10000 + Position) % 10000 as Col2R,
  max(convert(int, Col3 * 100) * 10000 + Position) % 10000 as Col3R
from T

Ok the ms access version.

SELECT
  max (  CInt( Col1 * 100) * 10000 + Position ) mod 10000 ,
  max (  CInt( Col2 * 100) * 10000 + Position ) mod 10000 ,
  max (  CInt( Col3 * 100) * 10000 + Position ) mod 10000 
FROM T

Upvotes: 2

June7
June7

Reputation: 21389

One option involves domain aggregate functions.

SELECT DISTINCT DLookUp("Position","Table3","Col1=" & DMax("Col1","Table3")) AS C1, 
DLookUp("Position","Table3","Col2=" & DMax("Col2","Table3")) AS C2, 
DLookUp("Position","Table3","Col3=" & DMax("Col3","Table3")) AS C3, 
DLookUp("Position","Table3","Col4=" & DMax("Col4","Table3")) AS C4
FROM Table3;

This might also be possible with correlated nested queries but it fries my brain thinking about it. I'll let you explore that possibility. Start with review of Find max value and show corresponding value from different field in MS Access

Another option involves VBA custom function. It would either use the domain aggregate expression above or open a recordset object.

Function GetMaxPos(strCol As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Position, " & strCol & " FROM Table3 ORDER BY " & strCol & " DESC")
GetMaxPos = rs!Position
End Function

Call the function from query or textbox.

SELECT DISTINCT GetMaxPos("Col1") AS C1, 
GetMaxPos("Col2") AS C2, 
GetMaxPos("Col3") AS C3, 
GetMaxPos("Col4") AS C4
FROM Table3;

Any of these options might perform slowly with 40 calculated values and great number of records.

Upvotes: 1

Related Questions