 | |
Help with query needed - adding over rows
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Help with query needed - adding over rows -
01-06-2012
, 11:11 AM
#2
| |||
| |||
|
|
I am trying to create a query (if possible) that will add certain numbers over a specified number of rows. The situation is a greenhouse in which trays of plant cuttings are placed for a number of weeks (the crop time) whilst they grow to the desired stage for delivery. *For example in the data below, for DeliveryWeek 7, the crop time is 6, so trays are stuck in StickWeek 1; the number of trays stuck is 8. *(Term 'stick' comes from cuttings being stuck into trays) For DeliveryWeek 8, 3 trays are stuck in week 2 Crop times are not constant through time because growing conditions change. For DeliveryWeek 12, 9 trays are stuck in week 7 Trays remain in the greenhouse for the duration of their crop time. I need to calculate the Capacity (shown here for illustration): in week 1 there are 8 trays. *In week 2, 8+3 = 11 trays. *In week 6, 8+5+3+8+6=30 In week 6, 8+2+9+6=25, in week 18, 6+1=7 DeliveryWeek * *CropTime * * * *StickWeek * * * TraysToStick * *Capacity 1 * * * * * * * * * * * * * * * 8 2 * * * * * * * * * * * * * * * 11 3 * * * * * * * * * * * * * * * 16 4 * * * * * * * * * * * * * * * 24 5 * * * * * * * * * * * * * * * 24 6 * * * * * * * * * * * * * * * 30 7 * * * 6 * * * 1 * * * 8 * * * 31 8 * * * 6 * * * 2 * * * 3 * * * 30 9 * * * 6 * * * 3 * * * 5 * * * 25 10 * * *6 * * * 4 * * * 8 * * * 26 11 * * *5 * * * 6 * * * 6 * * * 25 12 * * *5 * * * 7 * * * 9 * * * 20 13 * * *5 * * * 8 * * * 2 * * * 21 14 * * *4 * * * 10 * * *9 * * * 20 15 * * *4 * * * 11 * * *5 * * * 16 16 * * *4 * * * 12 * * *4 * * * 18 17 * * *4 * * * 13 * * *3 * * * 15 18 * * *4 * * * 14 * * *8 * * * 7 19 * * *4 * * * 15 * * *1 * * * 6 20 * * *4 * * * 16 * * *6 * * * 0 Is it possible to calculate the capacity by means of a query? *or do I need to resort to other methods? Any help would be welcome. |
datasheet, highlight, and then copy paste it into the message so
formatting would stay consistent. Probably those on a text based
system would have a mess but most should be able to see the data
formatted.
You wrote "In week 6, 8+5+3+8+6=30. Is week 5 skipped because the
diff between week 4 and 5 is 0?
You wrote "in week 18, 6+1=7:. I remain clueless.
If possible, I'd throw the results into a temp "make" table. If need
be I'd use a function.to calculate the capacity from the maketable
results. Or do a "DCount("SomeField","TableName","ID < " & [ID]) to
get the prior periods count and then subtract that from the current
value.
Since I need clarification on your issue ignore anything I wrote that
does not apply.
#3
| |||
| |||
|
|
I am trying to create a query (if possible) that will add certain numbers over a specified number of rows. The situation is a greenhouse in which trays of plant cuttings are placed for a number of weeks (the crop time) whilst they grow to the desired stage for delivery. For example in the data below, for DeliveryWeek 7, the crop time is 6, so trays are stuck in StickWeek 1; the number of trays stuck is 8. (Term 'stick' comes from cuttings being stuck into trays) For DeliveryWeek 8, 3 trays are stuck in week 2 Crop times are not constant through time because growing conditions change. For DeliveryWeek 12, 9 trays are stuck in week 7 Trays remain in the greenhouse for the duration of their crop time. I need to calculate the Capacity (shown here for illustration): in week 1 there are 8 trays. In week 2, 8+3 = 11 trays. In week 6, 8+5+3+8+6=30 In week 6, 8+2+9+6=25, in week 18, 6+1=7 DeliveryWeek CropTime StickWeek TraysToStick Capacity 1 8 2 11 3 16 4 24 5 24 6 30 7 6 1 8 31 8 6 2 3 30 9 6 3 5 25 10 6 4 8 26 11 5 6 6 25 12 5 7 9 20 13 5 8 2 21 14 4 10 9 20 15 4 11 5 16 16 4 12 4 18 17 4 13 3 15 18 4 14 8 7 19 4 15 1 6 20 4 16 6 0 Is it possible to calculate the capacity by means of a query? or do I need to resort to other methods? Any help would be welcome. |
#4
| ||||
| ||||
|
|
On Jan 6, 11:11*am, Keith <keith.tizz... (AT) pobox (DOT) com> wrote: I am trying to create a query (if possible) that will add certain numbers over a specified number of rows. The situation is a greenhouse in which trays of plant cuttings are placed for a number of weeks (the crop time) whilst they grow to the desired stage for delivery. *For example in the data below, for DeliveryWeek 7, the crop time is 6, so trays are stuck in StickWeek 1; the number of trays stuck is 8. *(Term 'stick' comes from cuttings being stuck into trays) For DeliveryWeek 8, 3 trays are stuck in week 2 Crop times are not constant through time because growing conditions change. For DeliveryWeek 12, 9 trays are stuck in week 7 Trays remain in the greenhouse for the duration of their crop time. I need to calculate the Capacity (shown here for illustration): in week 1 there are 8 trays. *In week 2, 8+3 = 11 trays. *In week 6, 8+5+3+8+6=30 In week 6, 8+2+9+6=25, in week 18, 6+1=7 DeliveryWeek * *CropTime * * * *StickWeek * * * TraysToStick * *Capacity 1 * * * * * * * * * * * * * * * 8 2 * * * * * * * * * * * * * * * 11 3 * * * * * * * * * * * * * * * 16 4 * * * * * * * * * * * * * * * 24 5 * * * * * * * * * * * * * * * 24 6 * * * * * * * * * * * * * * * 30 7 * * * 6 * * * 1 * * * 8 * * * 31 8 * * * 6 * * * 2 * * * 3 * * * 30 9 * * * 6 * * * 3 * * * 5 * * * 25 10 * * *6 * * * 4 * * * 8 * * * 26 11 * * *5 * * * 6 * * * 6 * * * 25 12 * * *5 * * * 7 * * * 9 * * * 20 13 * * *5 * * * 8 * * * 2 * * * 21 14 * * *4 * * * 10 * * *9 * * * 20 15 * * *4 * * * 11 * * *5 * * * 16 16 * * *4 * * * 12 * * *4 * * * 18 17 * * *4 * * * 13 * * *3 * * * 15 18 * * *4 * * * 14 * * *8 * * * 7 19 * * *4 * * * 15 * * *1 * * * 6 20 * * *4 * * * 16 * * *6 * * * 0 Is it possible to calculate the capacity by means of a query? *or do I need to resort to other methods? Any help would be welcome. I *wonder if one could place the figures in a spreadsheet or datasheet, highlight, and then *copy paste it into the message so formatting would stay consistent. *Probably those on a text based system would have a mess but most should be able to see the data formatted. |
that the layout it not sufficiently clear.
Quote:
|
You wrote "In week 6, 8+5+3+8+6=30. *Is week 5 skipped because the diff between week 4 and 5 is 0? |
8+5+3+8+0+6
Quote:
|
You wrote "in week 18, 6+1=7:. *I remain clueless. |
16, namely 6+1. Nothing was stuck in weeks 17 or 18 because there is
no demand in these later weeks. That stuck in week 14, namely 8 trays
has already left the greenhouse (it has a crop time of 4 weeks, and
stays for weeks 14,15,16 and 17)
Quote:
|
If possible, I'd throw the results into a temp "make" table. *If need be I'd use a function.to calculate the capacity from the maketable results. *Or do a "DCount("SomeField","TableName","ID < " & [ID]) to get the prior periods count and then subtract that from the current value. Since I need clarification on your issue ignore anything I wrote that does not apply. |
are right about trying a temporary table.
#5
| |||
| |||
|
|
A query returns data in tabular format, so it would really help if you would present the desired results from the proposed query in tabular format as well, explaining the results row-by-row if necessary. Keith wrote: I am trying to create a query (if possible) that will add certain numbers over a specified number of rows. The situation is a greenhouse in which trays of plant cuttings are placed for a number of weeks (the crop time) whilst they grow to the desired stage for delivery. *For example in the data below, for DeliveryWeek 7, the crop time is 6, so trays are stuck in StickWeek 1; the number of trays stuck is 8. *(Term 'stick' comes from cuttings being stuck into trays) For DeliveryWeek 8, 3 trays are stuck in week 2 Crop times are not constant through time because growing conditions change. For DeliveryWeek 12, 9 trays are stuck in week 7 Trays remain in the greenhouse for the duration of their crop time. I need to calculate the Capacity (shown here for illustration): in week 1 there are 8 trays. *In week 2, 8+3 = 11 trays. *In week 6, 8+5+3+8+6=30 In week 6, 8+2+9+6=25, in week 18, 6+1=7 DeliveryWeek CropTime StickWeek TraysToStick Capacity 1 8 2 11 3 16 4 24 5 24 6 30 7 6 1 8 31 8 6 2 3 30 9 6 3 5 25 10 6 4 8 26 11 5 6 6 25 12 5 7 9 20 13 5 8 2 21 14 4 10 9 20 15 4 11 5 16 16 4 12 4 18 17 4 13 3 15 18 4 14 8 7 19 4 15 1 6 20 4 16 6 0 Is it possible to calculate the capacity by means of a query? *or do I need to resort to other methods? Any help would be welcome. |
am trying to compute. Each entry in column 5 is the sum of column 4
over specified rows.
It is this summation that is causing me problems.
#6
| |||
| |||
|
|
On Jan 6, 5:51 pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote: A query returns data in tabular format, so it would really help if you would present the desired results from the proposed query in tabular format as well, explaining the results row-by-row if necessary. The first 4 columns are from a query. The 5th column is the result I am trying to compute. Each entry in column 5 is the sum of column 4 over specified rows. It is this summation that is causing me problems. |
rows got summed to result in 8? Delivery Week 7? What is the logic to tell
me that I should use Week 7?
In week 2, you said "8+3" ... where did the 3 come from? Delivery Week 8?
OK, let's follow this through. For weeks 1-4 this algorithm seems to hold.
Then we get to Week 5, where you show no change in capacity. ??? Why is
that? Hmmm , is it because of the stickweek value?
Wait, light is starting to dawn.
How about the sum of TraysToStick where StickWeek is less than or equal to
current DeliveryWeek? let me try that.
OK, that works until Week 7, where the algorithm results in 39, but your
desired result is 31 ... hmmm ... so we're supposed to subtract the
TraysToStick? 8 in Week 7? That would result in 31. Let's see if that works
in Week 8 ...
No, 41-3 does not equal 30. Hmmm ...
How about sum(traystostick where stickweek between current stickweek and
current deliveryweek) - current traystostick ... Yes, that seems to work.
For week 8, 33 - 3 = 30.
For week 9, 30 - 5 equals 25
Week 10, 34 - 8 equals 26
I think I've got it. If so, this algorithm can be further refined to:
sum(TraysToStick where StickWeek > Current StickWeek and StickWeek<= current
DeliveryWeek)
This will require a correlated subquery with a non-equi join. If the query
that returns the sample data is called qSrcData, this works:
SELECT s.DeliveryWeek, s.CropTime, s.StickWeek, s.TraysToStick
, Nz((select sum(TraysToStick) as capacity from qSrcData as q where
Nz(s.StickWeek,0) < q.StickWeek and q.StickWeek <= s.DeliveryWeek),0) As
Capacity
FROM qSrcData AS s;
#7
| |||
| |||
|
|
Keith wrote: On Jan 6, 5:51 pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote: A query returns data in tabular format, so it would really help if you would present the desired results from the proposed query in tabular format as well, explaining the results row-by-row if necessary. The first 4 columns are from a query. *The 5th column is the result I am trying to compute. *Each entry in column 5 is the sum of column 4 over specified rows. It is this summation that is causing me problems. OK, that makes it clearer, but ... where does the 8 in row 1 come from? What rows got summed to result in 8? Delivery Week 7? What is the logic to tell me that I should use Week 7? In week 2, you said "8+3" ... where did the 3 come from? Delivery Week 8? OK, let's follow this through. For weeks 1-4 this algorithm seems to hold.. Then we get to Week 5, where you show no change in capacity. ??? Why is that? Hmmm , is it because of the stickweek value? Wait, light is starting to dawn. How about the sum of TraysToStick where StickWeek is less than or equal to current DeliveryWeek? let me try that. OK, that works until Week 7, where the algorithm results in 39, but your desired result is 31 ... hmmm ... so we're supposed to subtract the TraysToStick? 8 in Week 7? That would result in 31. Let's see if that works in Week 8 ... No, 41-3 does not equal 30. Hmmm ... How about sum(traystostick where stickweek between current stickweek and current deliveryweek) - current traystostick ... Yes, that seems to work. For week 8, 33 - 3 = 30. For week 9, 30 - 5 equals 25 Week 10, 34 - 8 equals 26 I think I've got it. If so, this algorithm can be further refined to: sum(TraysToStick where StickWeek > Current StickWeek and StickWeek<= current DeliveryWeek) This will require a correlated subquery with a non-equi join. If the query that returns the sample data is called qSrcData, this works: SELECT s.DeliveryWeek, s.CropTime, s.StickWeek, s.TraysToStick , Nz((select sum(TraysToStick) as capacity from qSrcData as q where Nz(s.StickWeek,0) < q.StickWeek and q.StickWeek <= s.DeliveryWeek),0) As Capacity FROM qSrcData AS s; |
That's great. I think you have cracked it. I'll give it a try
Keith
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.