Help needed with INSERT INTO query 1

[VickyC]

Hello. I am given tables of integer values like this...

[b]tbl_OLD[/b]
[b]Old_ID   K1   K2   K3   K4   Outcome[/b]
   1     56   56   50   49     38
   2     56   52   52   44     28
   3     56   52   52   28     44
   4     56   50   49   38     56
   5     49   49   49   44     44
   6     49   49   49   42     60
   7     49   49   44   44     49 
   8     etc...

This table follows the following rules:
a) K1 >= K2 >= K3 >= K4 (The Outcome column does not necessarily follow this pattern)
b) Each record (K1, K2, K3, K4, Outcome) is distinct
c) The table is ordered by K1 DESC, K2 DESC, K3 DESC, K4 DESC.


My task is to REWRITE these 5 values into a table like this...

[b]tbl_NEW[/b]
[b]New_ID   Ka   Kb   Kc   Kd   Ke[/b]       notes...
 101     60   49   49   49   42       This is Old_ID = 6, reordered
 102     56   56   50   49   38       This is Old_ID = 1,  Old_ID = 4 was removed (duplicate)
 103     56   52   52   44   28       This is Old_ID = 2,  Old_ID = 3 was removed (duplicate)
 104     49   49   49   44   44       This is Old_ID = 5,  Old_ID = 7 was removed (duplicate)
 105     etc...

This NEW table follows the following rules:
a) Ka >= Kb >= Kc >= Kd >= Ke
b) DUPLICATES are REMOVED
c) The table is ordered by Ka DESC, Kb DESC, Kc DESC, Kd DESC, Ke DESC

In other words, the Outcome value is placed amongst the 4 K values, and duplicates are removed.

I realize that the tables I am given are not Normalized, but I could do this if required.
My current solution uses recordsets in VBA, but I find it slow when processing very large tables. I've been trying to do this using SQL, but no luck so far.

Is a SQL solution possible?

Any help is appreciated. Vicky

 

[PHV]

A starting point:

SELECT K1 AS Ka,K2 AS Kb,K3 AS Kc,K4 AS Kd,Outcome AS Ke
FROM tbl_OLD WHERE Outcome<=K4
UNION SELECT K1,K2,K3,Outcome,K4
FROM tbl_OLD WHERE Outcome between K3 And K4
UNION SELECT K1,K2,Outcome,K3,K4
FROM tbl_OLD WHERE Outcome between K2 And K3
UNION SELECT K1,Outcome,K2,K3,K4
FROM tbl_OLD WHERE Outcome between K1 And K2
UNION SELECT Outcome,K1,K2,K3,K4
FROM tbl_OLD WHERE Outcome>=K1
ORDER BY 1 DESC,2 DESC,3 DESC,4 DESC,5 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks PHV - that was exactly the insight I needed. My actual tables have can have 5 to 7 columns, but this approach works like a charm!
Vicky C.