Counting issue in Access

[Cecil Rhoades]

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.

[charlotte]

What are you trying to accomplish? Are you trying to populate a form or report, view the query, or what? Basing analysis on sequence is slow and creaky at best and you may have to stack your queries rather than trying to use subqueries. If you post the entire query instead of just bits and pieces someone may be able to help. You also need to tell us what version of Access and Windows you're running.

[Cecil Rhoades]

Purpose is to provide a report to production, so that material from a sub assembly area will be delivered in the order of use, to the assembly area.

Originally (at the time of my first post) I tried using a sub query in a query with joined tables. I created 4 queries, as you put it, stacked. The fisrt 3 delt with table 1, first applying the ordinal number, the second, breaking the data into boxes and layers, the third ordering within the layers and the final, join the whole mess.

Yes, it is slow, but it will run in batch at night and does complete in less than 4 minutes.

FYI, using Access 97 on NT4.

Thanks for the advise.

[charlotte]

Another approach might be to use subreports based on the individual queries and see if that speeds it up any.