3-Up Sort Order

[Joshua A. Booker]

Hi There,

I have a report that prints slips 3-up on a page. The report is very long
and the slips get cut so each page is cut into 3 slips. I'd like to sort
the report so when I cut them they stack in the correct order.

More details:

Let's say I have 9 records to print. Rather than printing records 1, 2, 3
on page one. I'd like to print records 1, 4, 7 on page one. Then print 2,
5, 8 on page two and 3, 6, 9 on page three. That way I can cut them
together and when I stack the three piles they'll be in the correct order.

Please let me know if this makes sense.

Thanks in Advance,
Josh

 

[Pat Hartman\(MVP\)]

You'll need to create a new sort order field to create a custom sequence.

 

[Allen Browne]

Hi Josh

I did go through this process with someone a few months back.

The solution involves getting a sequential number into the query. For
example if the records are sorted by primary key ID, use:
SeqNum: Nz(DCount("*", "Table1", "ID < " & [ID]),0)

You can then use \ (the integer division operator) and Mod (the remainder
after division) to determine the position of the item in your report. The 2
calculated fields would be:
LeftOver: [SeqNum] Mod 3
and:
IntDiv: [SeqNum] \ 3

Now in the report's sorting'n'grouping dialog, you can sort by:
LeftOver
IntDiv

That should give you the right order, if the number of records is a multiple
of 3.

 

[Joshua A. Booker]

Allen,

Thanks for pointing me in the right direction.

I used your advice and found that the mod 3 and \3 only work if there are
three pages of results. Turns out the example I used in my post was very
simple compared to the actual problem. I need this to work for a variable
number of records the count of which may not be a multiple of 3.

Anyway, thanks to your direction, I found the following solution was good
with only a couple exceptions:

Sort1 = [Sequence] Mod [Pages]
Sort2 = [Sequence]/[Pages]

Exceptions:

The last page is when ([Sequence] Mod [Pages])=0 which sorts first using
Sort1.
If the recordcount is not a mutiple of 3, then the last record or two sort
before the second page.

I added a couple of IIFs to handle these and it seemd to sort right in the
query so I'll try it on the report and see how it prints.

Here is the SQL:

SELECT qry_Sequence.Sequence, DCount("*","qry_Sequence") AS Records, 3 AS
PerPage, [Records]\[PerPage] AS Pages, [Sequence]<=[PerPage]*[Pages] AS
FullPage, [Sequence] Mod [Pages] AS Mod,
IIf([Mod]=0,[Pages],IIf([FullPage],[Mod],[Mod]+[Pages])) AS Sort1,
[Sequence]/[Pages] AS Sort2
FROM qry_Sequence;

Thanks again,
Josh

Hi Josh

I did go through this process with someone a few months back.

The solution involves getting a sequential number into the query. For
example if the records are sorted by primary key ID, use:
SeqNum: Nz(DCount("*", "Table1", "ID < " & [ID]),0)

You can then use \ (the integer division operator) and Mod (the remainder
after division) to determine the position of the item in your report. The
2 calculated fields would be:
LeftOver: [SeqNum] Mod 3
and:
IntDiv: [SeqNum] \ 3

Now in the report's sorting'n'grouping dialog, you can sort by:
LeftOver
IntDiv

That should give you the right order, if the number of records is a
multiple of 3.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that prints slips 3-up on a page. The report is very
long and the slips get cut so each page is cut into 3 slips. I'd like to
sort the report so when I cut them they stack in the correct order.

More details:

Let's say I have 9 records to print. Rather than printing records 1, 2,
3 on page one. I'd like to print records 1, 4, 7 on page one. Then print
2, 5, 8 on page two and 3, 6, 9 on page three. That way I can cut them
together and when I stack the three piles they'll be in the correct
order.

Please let me know if this makes sense.

Thanks in Advance,
Josh