Sort across a table

  • Thread starter Thread starter The Viking
  • Start date Start date
T

The Viking

I have a table with theese records:

SpillID SumBS SumGG SumBB SumBH
1 66 42 53 21
2 60 22 35 32
3 52 21 20 14
4 59 10 4 46
5 -8 53 17 -1
6 39 18 49 53
7 17 50 54 34
8 24 53 38 56
9 25 44 35 53
10 55 11 -9 32

Are there a way that Access that can present these data sorted like this:

SpillID
1 66 53 42 21
2 60 35 32 22
3 52 21 20 14
4 59 46 10 4
5 53 17 -1 -8
6 53 49 39 18
7 54 50 34 17
8 56 53 38 24
9 53 44 35 25
10 55 32 11 -9

Any answer will be highly valued.
 
hi,

I have a table with theese records:
SpillID SumBS SumGG SumBB SumBH
1 66 42 53 21
Click to expand...
Are there a way that Access that can present these data sorted like this:

SpillID
1 66 53 42 21
Any answer will be highly valued.
Click to expand...
This makes no sense, at last for me. As you are mixing the result types.

You need a "denormalized" query for calculating your sums, then you can
use a crosstab query on it.

E.g. if your actual record source looks like

SELECT SpillID, Sum(BS) AS SumBS, Sum(GG) AS SumGG
FROM yourTable
GROUP BY SpillID;

The you need to denormalize it:

SELECT SpillID, Sum(BS) AS [Sum]
FROM yourTable
GROUP BY SpillID
UNION ALL
SELECT SpillID, Sum(GG) AS [Sum]
FROM yourTable
GROUP BY SpillID;

Append an artifical order:

SELECT SpillID, [Sum],
DCount("*", "denomQuery",
"SpillID = " & SpillID & " AND [Sum] < " & [Sum])
AS RowNum
FROM denomQuery
ORDER BY RowNum;

Built a crosstab over SpillID and RowNum.


mfG
--> stefan <--
 
Try these queries --
Spills_1
SELECT Spills.SpillID, 1 AS XX, Spills.SumBS AS [YY]
FROM Spills
UNION ALL SELECT Spills.SpillID, 2 AS XX, Spills.SumGG
FROM Spills
UNION ALL SELECT Spills.SpillID, 3 AS XX, Spills.SumBB
FROM Spills
UNION ALL SELECT Spills.SpillID, 4 AS XX, Spills.SumBH
FROM Spills;

Spills_1
SELECT Spills_1.SpillID, Spills_1.YY, (SELECT Count(*) FROM Spills_1 AS
[AA] WHERE [AA].SpillID = Spills_1.SpillID AND [AA].YY >= Spills_1.YY ) AS BB
FROM Spills_1
ORDER BY Spills_1.SpillID, Spills_1.YY DESC;

SELECT Spills_2.SpillID, Spills_2.[YY] AS Row_1, Spills_2_1.YY AS Row_2,
Spills_2_2.YY AS Row_3, Spills_2_3.YY AS Row_4
FROM ((Spills_2 LEFT JOIN Spills_2 AS Spills_2_1 ON Spills_2.SpillID =
Spills_2_1.SpillID) LEFT JOIN Spills_2 AS Spills_2_2 ON Spills_2.SpillID =
Spills_2_2.SpillID) LEFT JOIN Spills_2 AS Spills_2_3 ON Spills_2.SpillID =
Spills_2_3.SpillID
WHERE (((Spills_2.BB)=1) AND ((Spills_2_1.BB)=2) AND ((Spills_2_2.BB)=3) AND
((Spills_2_3.BB)=4))
ORDER BY Spills_2.SpillID;
 
Back
Top