1

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

Rod Parker
  • 11
  • 2
  • Seems you are wanting a crosstab query to get pivoted data. https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/transform-statement-microsoft-access-sql. – Tim Jul 27 '21 at 05:25
  • Do you import the table with SQL and VBA? – TimLer Jul 27 '21 at 05:25
  • How many records are involved? Could values be repeated in a column? If yes, which position would you want? – June7 Jul 27 '21 at 05:42
  • SQL or VBA, doesn't matter, either works for me. Records = 200k+, numbers repeating shouldn't really matter, all numbers are to 6 dec places. Thanks to everyone so far, I am working through some of the answers now. – Rod Parker Jul 27 '21 at 06:25
  • I have tried all the responses and at first all seemed to work weel however when I add more data I get incorrect results. – Rod Parker Jul 27 '21 at 10:44

3 Answers3

2

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
AIMIN PAN
  • 1,563
  • 1
  • 9
  • 13
1

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.

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thank you so much, your 'domain aggregate function' works perfectly. I will stick with the SQL as it natively works a little quicker than VBA. Forever greatful to evryone who helped out. – Rod Parker Jul 27 '21 at 06:42
  • Suggest you try approach in other answer also to compare. I am impressed, never would have thought of it. – June7 Jul 27 '21 at 07:05
0

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.

Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26