Naming records after column their found in

  • Thread starter Thread starter David M
  • Start date Start date
D

David M

Hello,

I've got a bit of a problem regarding a table that I have.

The table has 11 columns and 1042 records. (The big table
has 1.7 Million records.)

Stored in each of the rows is numerical data from 0 to 1.

For example:

A B C D E F G H I J K
0.1 0.3 0.15 0.32 0.34 0.43 0.9 0.76 0.64 0.77 0.3

What I want to do is select the maximum value for each
record (which in the above case is 0.9). I then want to
either append, or create a new table that states which
column that this value comes from. So, for the above
example where the maximum value is 0.9 I want to paste "G"
as my query result.

I would appreciate any help in this.

Cheers,

David Mitchell
 
Hi,





SELECT id, MAX(theValue)


FROM ( SELECT "A" As id, A As theValue FROM myTable
UNION ALL
SELECT "B" , B FROM myTable
UNION ALL
SELECT "C" , C FROM myTable
UNION ALL
SELECT "D" , D FROM myTable
UNION ALL
SELECT "E" , E FROM myTable
....
UNION ALL
SELECT "K" , K FROM myTable)


GROUP BY id





Hoping it may help,
Vanderghast, Access MVP
 
Michel (and any other who want to contribute)

I put in the previous query and this is the result I got

A 0.73
B 0.9487
C 0.4576
D 0.9
E 0.986
F 0.45
G 0.12
H 0.7
I 0.8
J 0.3674
K 0.675

This was based on a trial database I contructed with 2 rows

G H J K E F D B A C
0.123 0.73 0.1235 0.6756 0.9866 0.456 0.92 0.345 0.731 0.23 0.8
0.125 0.56876 0.36746 0.47658 0.478564 0.3765 0.37658 0.94875 0.34635 0.45765

The answers I'm looking for in my query would be

E 0.986
B 0.9487

Ie: The name of the column where the maximum value resides for each record (or row)

Kindest regards

David Mitchell
 
I would add an autonumber primary key to your table (myTable) and add the
field to your union query
SELECT [NewID], "A" As id, A As theValue
FROM myTable
UNION ALL
SELECT [NewID],"B" , B FROM myTable
UNION ALL
SELECT [NewID], "C" , C FROM myTable
UNION ALL
SELECT [NewID], "D" , D FROM myTable
UNION ALL
SELECT [NewID], "E" , E FROM myTable
....
UNION ALL
SELECT [NewID], "K" , K FROM myTable;
You can then create a new query
SELECT NewID, Max(thValue) as MaxVal
FROM quniYourQuery
GROUP By NewID;
 
Question: What do you want to return if two of the 11 columns are equal and they
are the maximum?

I would probably tackle this in one of two ways. One way would be to write a
vba function to step through all the records and create the new records in a new table.

Second would be to
Add an autonumber field to the table.
Build a "normalizing" Union query
Then use that as the source to get the maximum value and the correct column
Then use that as the source for an insert query.

SELECT NewIDCol, "A" as ColName, A
FROM Table
UNION ALL
SELECT NewIDCol, "B", B
FROM Table
....
SELECT NewIDCol, "K" , K
FROM Table

Save that as QryN

Next Step

SELECT NewIDCol, Max(A) as MaxA
FROM QryN
GROUP BY NewIDCol

Save that as qryN1

Next Step:

SELECT qryN.ColName, qryN.A
FROM qryN INNER Join QryN1
ON QryN.NewIdCol = qryN1.NewIdCol
qryN.A = qryN1.MaxA

Of course, most of that can probably be combined into one query, but it will
probably be faster if you use the step approach - especially if you are using
Access + Jet (an .mdb).
 
Back
Top