Need help with query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 columns.
Example.

ID CK KN Sum

1 SE 6111.2090 643,11
1 SE 6209.2090 848,69
1 LV 6104.4200 1156,03
1 PT 6104.4200 433,65
1 IT 6115.1900 564,20
1 IT 6210.5000 247,50
2 SE 6109.1000 1258,28
2 SE 6209.2000 2189,01
2 SE 6404.2010 523,77
2 FI 6111.2090 849,03
2 LT 6505.9010 402,00
2 LT 6111.2090 905,21

I want it to select the KN for each CK with the highest sum.
I also need the total sum for all KN per CK even if I only need to know the
KN that has the highest sum.

Each ID can have several CK.
Each CK can have several KN.
Each KN can have several Sum.

The result would be.

ID CK KN Sum

1 SE 6209.2090 1491,80
1 LV 6104.4200 1156,03
1 PT 6104.4200 433,65
1 IT 6115.1900 811,70
2 SE 6209.2000 3971,06
2 FI 6111.2090 849,03
2 LT 6111.2090 1307,21

I hope you understand what I mean.
Pls ask if you dont.

/Claes
 
I would create two queries, one to return the ID,CK,MaxOfSum,SumOfSum
' Change the name of the field Sum, it a resurved name in Access, I changed
it to MySum, and saved the Query as MyQuery

SELECT MyTableName.Id, MyTableName.CK, Max(MyTableName.MySum) AS MaxOfMySum,
Sum(MyTableName.MySum) AS SumOfMySum
FROM MyTableName
GROUP BY MyTableName.Id, MyTableName.CK
=================================================
Now second query link the table to the first query

SELECT MyTableName.Id, MyTableName.CK, MyTableName.KN, MyQuery.SumOfMySum
FROM MyQuery INNER JOIN MyTableName ON (MyQuery.MaxOfMySum =
MyTableName.MySum) AND (MyQuery.CK = MyTableName.CK) AND (MyQuery.Id =
MyTableName.Id)
==================================================
 
Thank you for your help.
I will try this tonight.

I name everything in Swedish, so I just made up some names.
They are not the ones that are used, but thanks for the tip neway.

BR
Claes

"Ofer" skrev:
 
Back
Top