So it's one page per truck, right? Is the Door# the key to identifying the truck/container? If you give me the actual layouts of the tables, I can do the sample SQL much quicker.
This seems like an appropriate place for a tricky crosstab.
Here's the high concept:
You create a table that looks like this:
Code:
tblTruckLayout
Slot Number (1 thru 22)
Side Text (L or R)
Pad Number (1 thru 11)
Remain Number (0 thru 10 - number of pads left behind this slot)
You fill it with this static data. (finish the obvious pattern.) The reason for including the "Remain" column is that it will help to code tests for if the fifth set of pallets rolled to the right.
Code:
SLOT SIDE PAD REMAIN
1 L 1 10
2 L 2 9
3 L 3 8
4 L 4 7
5 L 5 6
.... ... .... ...
10 L 10 1
11 L 11 0
12 R 1 10
13 R 2 9
14 R 3 8
.... ... .... ...
21 R 10 1
22 R 11 0
You create a query that makes this layout, or a VBA routine that loads a temp table with this layout:
Code:
qryTruckLoad
TruckID Text (Truck name or Key)
Slot Number (1 to 22)
Contents Text (Store name or Store name and pallet data)
I can help you figure out how to do that, but I'd need the actual layouts to produce anything workable. Ideally, if you post a set of fifteen to twenty pallets worth of sample data for one Door# on one morning, then I can get pretty close.
Then you use a crosstab query like this to produce your report:
Code:
TRANSFORM First(Contents) As TheContents
SELECT TruckID, Pad
FROM
tblTruckLayout AS T1
INNER JOIN
qryTruckLoad AS Q1
on Q1.Slot = T1.Slot
PIVOT T1.Side;