I need to stratify some data. I need to load boxes in reverse order. Each box contains 4 layers, each layer 6 parts. Therefore the BOX 1 contains Layer 1, parts 24-19, Layer 2, parts 18 - 13, layer 3, parts 12 - 7, parts 6-1. BOX 2 contains parts 48-25, in layers of course.
My approach was to count the parts in a sub query, (SELECT COUNT(*) FROM MyTable as t1 WHERE (ProductionDate = MyTable.ProductionDate) AND (Seq < MyTable.Seq)[img]/forums/images/smilies/wink.gif[/img] as Sequence. This works.
Next, I use some math to break this into buckets of 24, INT(Sequence/24) +1 as Box. This works.
Next I try to repeat the subquery against the calculated field. However, I get the error "The instruction at [some address] cannot be read."
I also tried some math to reverse the box fill order.
24-([Sequence]-(Int([Sequence]/24)*24)), which works if all the boxes are full, but if the final box is partial, the count does not start with one.
Any Ideas? Thanks.