Sum problem

[Ipswmaniac]

Hello
Im new to access and uptil now have only used the help files to find my way around but i havent been able to solve.

Here is the query example hope it makes sence

field1 field2

item1 17
item2 23
item3 12
item4 7
item5 29

What i want it to do is limit items shown based on count of values in field2

Say i wanted only 55 items shown i would end up with this shown

field1 field2

item1 17
item2 23
item3 12
item4 3

 

[RainLover]

What would you do if item3 was 11 and item5 was 9

 

[Ipswmaniac]

What would you do if item3 was 11 and item5 was 9

If item3 was 11 i would end up with

field1 field2
item1 17
item2 23
item3 11
item4 4

and item5 wouldnt show because i was only after 55 items

 

[RainLover]

I am not totally understanding the situation.

Please clarify if you want this for a Form or a Report?

Could you post a cut down Database with this table.

 

[Ipswmaniac]

Its for a combo box on a form

qryArmour
SELECT Armour.Name, Armour.Attack, Armour.Defence, Armour.[No]
FROM Armour (table)
ORDER BY Armour.Attack DESC;

I only want the combo box to display the Armour with the highest attack values but limited to 500 total number of armours (confusing myself even)

example

Armour1 has 54 attack and i have 230 of them
Armour2 has 48 attack and i have 120 of them
Armour3 has 45 attack and i have 35 of them
Armour4 has 40 attack and i have 135 of them
Armour5 has 38 attack and i have 65 of them
and so on

based on only top 500 armours i would get on my combo box

Armour1 230
Armour2 120
Armour3 35
Armour4 115

Armour4 would only show 115 because armour 1,2 and 3 = 385, giving me my top 500 armours

Could you post a cut down Database with this table.

How do i post a cut down Database?

I should mention im using access 97

 

[jal]

You say you want it based on the top values (in DESC order). But then you wrote this sample which is NOT in exact DESC order:

Armour1 has 54 attack and i have 230 of them
Armour2 has 48 attack and i have 120 of them
Armour3 has 45 attack and i have 35 of them
Armour4 has 40 attack and i have 135 of them
Armour5 has 38 attack and i have 65 of them

So the first problem is that you seem to be contradicting yourself. The second problem is that you want to alter a value on the fly based on a computation - you say if the sum is more than 500, fudge the value on the last row to get it down to 500. That sounds a little tricky for a query - it would probably be easier to do this in VBA. Anyway, as far as the first part (pulling those rows whose sum is less than 500), try this (this builds two extra columns called RunningTotal and Ordinal).

SELECT A.Name, A.Attack, A.Defence, A.[No], (SELECT Sum(A2.Attack) FROM Armour as A2 WHERE A.Attack <= A2.Attack) as RunningTotal, (SELECT Count(A3.Attack) FROM Armour as A3 WHERE A.Attack <= A3.Attack) As Ordinal
FROM Armour as A
ORDER BY A.Attack DESC;

Save this one as qryRunningTotals. So that's a starting point. As for the tricky part, not sure I'll have time to look into that....

 

[jal]

Just upload the whole db if it's less than 100 MB (preferably zip it) - only do this if the data isn't private material. If it's more than 100 mb (I think that's the limit on this forum), you'll have to remove some tables or data.

Actually I put your sample data in a spreadsheet - that should be good enough for anyone who wants to try it.

 

[jal]

Ok, here's a solution using a VBA recordset - that's the best I can do at the moment.

 

[Ipswmaniac]

Thank you Jal

Your help has steered me in the right direction i think and also helped me understand recordsets which till now i wasnt sure how to use.

Your code is pretty close to what i was looking for and once i have it working how i hope it to i will post it

Thanks again for your help

 

[Ipswmaniac]

Here is the code and it works pretty good so far thanks to Jal
I had to modify the If statement a little cause it kept updating the last recordset read on the table
If anyone wants to look at db i wouldnt mind some suggestions on how i can reuse this code for my Armour table and Vehicle table which will have lists on same form


Dim rs As DAO.Recordset
Private Sub Main()
CurrentDb.Execute "UPDATE tblWeapons SET Include = False"
Dim total As Long
Dim count
Do Until rs.EOF
count = count + 1
total = total + rs("No")
rs.Edit
If total <= 500 Then rs("Include") = rs("No")
If total = 500 Then Exit Do
If total > 500 Then
rs("Include") = (rs("No") - (total - 500))
rs.Update
Exit Do
End If
rs.Update
rs.MoveNext
Loop
Set rs = Nothing
Me.List0.Height = count * 260.8
End Sub
Private Sub ListAttackDesc_Click()

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblWeapons ORDER BY Attack DESC")
Main
Me.List0.RowSource = "SELECT tblWeapons.Name, tblWeapons.Attack, tblWeapons.Defence, tblWeapons.Include FROM tblWeapons WHERE (((tblWeapons.Include)=True)) ORDER BY tblWeapons.Attack DESC;"

ArmNo1Label.Caption = "Att"
ArmNo2Label.Caption = "Def"
TotalLabel.Caption = "Total Attack"
End Sub
Private Sub ListDefenceDesc_Click()

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblWeapons ORDER BY Defence DESC")
Main
Me.List0.RowSource = "SELECT tblWeapons.Name, tblWeapons.Defence, tblWeapons.Attack, tblWeapons.Include FROM tblWeapons WHERE (((tblWeapons.Include)=True)) ORDER BY tblWeapons.Defence DESC;"

ArmNo1Label.Caption = "Def"
ArmNo2Label.Caption = "Att"
TotalLabel.Caption = "Total Defence"
End Sub

 

[Ipswmaniac]

Above code has a bug in If statements

If the last rs("no") read brings total to 500 then this

If total <= 500 Then rs("Include") = rs("No") fires

then so does this

If total = 500 Then Exit Do

but it exits do and doesnt update the rs
Ive tryed changing things around but cant get it to work and thought maybe best bet is this

If total = 500 Then rs.Update Exit Do

but i dont know the correct syntax or if this is possible for a single line statement

 

[Ipswmaniac]

Select Case solved the problem

Do Until rs.EOF
CountListHeight = CountListHeight + 1
total = total + rs("No")
rs.Edit

Select Case total
Case Is < 500
rs("Include") = rs("No")
rs.Update
Case 500
rs("Include") = rs("No")
rs.Update
Exit Do
Case Is > 500
total = total - 500
rs("Include") = (rs("No") - total)
rs.Update
Exit Do
End Select

rs.MoveNext
Loop