I suspect your data is perhaps simplified but do the following will give you the box and position for each candle.
1. create a table called admCounter, populated like this - datatype is a long number
2. copy and paste this sql into a new query and call it admCount
Code:
SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS singles, admCounter AS tens, admCounter AS hundreds, admCounter AS thousands;
3. Make sure your table looks like this (I've called mine Table1, but you might call it tblOrders)
Season |
Qty |
SeasonOrder |
spring |
5 |
1 |
summer |
5 |
2 |
autumn |
4 |
3 |
winter |
6 |
4 |
4. Copy this sql to a new query - change table name if required
Code:
SELECT Table1.Season, Table1.Qty AS SeasonTtl, [Counter]+Nz((SELECT sum(qty) FROM Table1 T where seasonorder<table1.seasonorder),0) AS ID, (([id]-1)\12)+1 AS Box, (([id]-1) Mod 12)+1 AS [Position]FROM admCount, Table1
WHERE (((admCount.Counter)>0 And (admCount.Counter)<=[qty]))
ORDER BY Table1.SeasonOrder, admCount.Counter;
which produces this result
Season |
SeasonTtl |
ID |
Box |
Position |
spring |
5 |
1 |
1 |
1 |
spring |
5 |
2 |
1 |
2 |
spring |
5 |
3 |
1 |
3 |
spring |
5 |
4 |
1 |
4 |
spring |
5 |
5 |
1 |
5 |
summer |
5 |
6 |
1 |
6 |
summer |
5 |
7 |
1 |
7 |
summer |
5 |
8 |
1 |
8 |
summer |
5 |
9 |
1 |
9 |
summer |
5 |
10 |
1 |
10 |
autumn |
4 |
11 |
1 |
11 |
autumn |
4 |
12 |
1 |
12 |
autumn |
4 |
13 |
2 |
1 |
autumn |
4 |
14 |
2 |
2 |
winter |
6 |
15 |
2 |
3 |
winter |
6 |
16 |
2 |
4 |
winter |
6 |
17 |
2 |
5 |
winter |
6 |
18 |
2 |
6 |
winter |
6 |
19 |
2 |
7 |
winter |
6 |
20 |
2 |
8 |