Select records based on running total of a field 1

[Poduska]

I have three fields Store, Item # and Qty. sorted by Store and descending by quantity. I wish to select records for each store until my QTY totals a value, say 1000. Any records after that for a store I wish to disregard. Now I realize I could do this in Excel but with 250,000 lines...

Perhaps this needs to be coded and not SQL, what do you think? This at first appears to be simple but I can't seem to get anything to work.

Sample data
Store item qty total per store
1 10010 20 20
1 10020 40 60
1 10030 60 100
1 10040 80 140
1 10050 100 180
1 10060 120 220
1 10070 140 260
1 10080 160 300
1 10090 180 340
1 10100 200 380
1 10110 220 420
1 10120 240 460
1 10130 260 500
1 10140 280 540
2 10010 20 20
2 10020 40 60
2 10030 60 100
2 10040 80 140
2 10050 100 180
2 10060 120 220
2 10070 140 260
2 10080 160 300
2 10090 180 340
2 10100 200 380
2 10110 220 420
2 10120 240 460
2 10130 260 500
2 10140 280 540

Thanks
David

 

[dRahme]

Hello, the following should get you started. You will need an autonumber. Pkey is the autonumber in this example:

SELECT Table1.Pkey, Table1.Store, Table1.Qty, DSum("qty","Table1","[Store] & [store] = '" & [Store] & [store] & "' and [Pkey] <= " & [Pkey]) AS Rsum
FROM Table1
WHERE (((DSum("qty","Table1","[Store] & [store] = '" & [Store] & [store] & "' and [Pkey] <= " & [Pkey]))<=1000))
ORDER BY Table1.Store, Table1.Qty;

Run a delete query based on the Pkey's not in this query.

Hope that helps.

 

[Poduska]

WOW, I will have to take a long look at WHY it works, but it does!!!
I never have quite grasped Dsum but now is the time to learn.

Thanks again, SAVED by another Tek-Tip master

David

 

[dRahme]

You're welcome and thanks for the star.

dRahme

 

[NPSH123]

Dumb question:
In the Dsum statement(s), store appears twice...what is the significance of it appearing twice, and what is the significance of the capitalization thing?

DSum("qty","Table1","[Store] & [store] = '" & [Store] & [store] & "' and [Pkey] <= " & [Pkey])

Many, many thanks!