If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sort across a table
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. |
#2
|
|||
|
|||
Sort across a table
hi,
On 15.02.2010 12:14, The Viking wrote: I have a table with theese records: SpillID SumBS SumGG SumBB SumBH 1 66 42 53 21 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. 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 -- |
#3
|
|||
|
|||
Sort across a table
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; -- Build a little, test a little. "The Viking" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|