Sort ready for cutting

[Alexg400]

If I have a report that generates 8 'cards' to a page and I print 100 pages
for example, can I get access to sort the cards in such a way that when the
100 sheets are cut in one go with a guillotine the 8 stacks of cards are in
sequence and all that remains is to stack the 8 stacks together? At the
moment I have to hand sort the 8 stacks which is very time consuming. (Access
'03)

 

[John Spencer]

Perhaps. It depends on a number of factors.

Is each "card" a record?
Does each record have a unique value or set of values that you can use to
order the records?

If so you can use a ranking query and integer division to generate the page
sort order. For example:

SELECT <Field List>
, (SELECT Count(*)
FROM YourTable as Sa
WHERE Sa.FieldUnique < YourTable.FieldUnique) \ 8 As PageOrder
FROM YourTable


That would generate a value of
0 for records 1 to 8
1 for records 9 to 16
....

If you need more help building this, tell us how the records are currently
sorted - that is the sort order.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Alexg400]

Thanks for taking the time to think about this.

Each "Card" is a set of 3 records of which the 3rd record is the sort order.
I'm only a learner with Access so forgive me if that's not what you meant.

Basically given the 8 cards per page and lets say for simplicity's sake 10
pages, the actual sequence of the report layout for page one would be:

1st, 11th, 21st, 31st, 41st, 51st, 61st, 71st.

Page two would be:

2nd, 12th, 22nd, 32nd, 42nd, 52nd, 62nd, 72nd

and so on, giving me eight vertical stacks ready to be placed on top of one
another.

It's one of those complicated but useful things that to be fair to MS they
usually think of in their wizards.

 

[Chuck]

If I have a report that generates 8 'cards' to a page and I print 100 pages
for example, can I get access to sort the cards in such a way that when the
100 sheets are cut in one go with a guillotine the 8 stacks of cards are in
sequence and all that remains is to stack the 8 stacks together? At the
moment I have to hand sort the 8 stacks which is very time consuming. (Access
'03)

Are you looking for something like this?
Sheet 1, Card Numbers
1 101
201 301
401 501
601 701

Sheet 2, Card Numbers
2 102
202 302
402 502
602 702

Sheet 100, Cardnumbers
100 200
300 400
500 600
700 800

Chuck
--

 

[Alexg400]

Yes, you got it.

Are you looking for something like this?
Sheet 1, Card Numbers
1 101
201 301
401 501
601 701

Sheet 2, Card Numbers
2 102
202 302
402 502
602 702

Sheet 100, Cardnumbers
100 200
300 400
500 600
700 800

Chuck

 

[Alexg400]

Yes you got it. Each card has 3 records of which I want to sort by the 3rd

 

[Chuck]

If I have a report that generates 8 'cards' to a page and I print 100 pages
for example, can I get access to sort the cards in such a way that when the
100 sheets are cut in one go with a guillotine the 8 stacks of cards are in
sequence and all that remains is to stack the 8 stacks together? At the
moment I have to hand sort the 8 stacks which is very time consuming. (Access
'03)

SQL statement for 8 cards per page and 100 pages:

SELECT 8*([Record Number]-1)+1-799*(Int(([Record Number]-1)/100)) AS SortOrder,
your table name.[Record Number]
FROM your table name
ORDER BY 8*([Record Number]-1)+1-799*(Int(([Record Number]-1)/100));

Just a wizard prodder
Chuck
--

 

[John Spencer]

You still haven't given us any details on the records and how they are
sorted.

How do you decide which three records go on card 1? You can't sort record 1
and record 2 by a value in record 3.
Card 1
Record 1
Record 2
Record 3

Card2
Record 4
Record 5
Record 6

What decides the order of the records (what fields)? What type are the
types of the fields involved? Do records 1, 2, and 3 share some common
field values that are different than the same values in records 4, 5, and 6?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chuck]

You still haven't given us any details on the records and how they are
sorted.

How do you decide which three records go on card 1? You can't sort record 1
and record 2 by a value in record 3.
Card 1
Record 1
Record 2
Record 3

Card2
Record 4
Record 5
Record 6

What decides the order of the records (what fields)? What type are the
types of the fields involved? Do records 1, 2, and 3 share some common
field values that are different than the same values in records 4, 5, and 6?

I suspect that he has 3 fields rather than 3 records, however, the number of
records is unimportant so long as all the records that go on one card have the
same card number.

Chuck
--

 

[Alexg400]

Thanks Chuck, I'll see if I can give that a go.

I've worked out a temporary 'bodge' by adding an invisible 4th field to the
report which is generated in excel and cut and pasted over. Requires a bit of
maths but does the trick.

Your way may require less messing about in the long run however.

 

[Chuck]

On Wed, 27 Jun 2007 09:30:03 -0700, Alexg400

There's always more than one way to solve a problem. I went for quick and
easy. There's almost certainly a better way if someone wants to put a little
more thought into it than I did.

Chuck
--

Thanks Chuck, I'll see if I can give that a go.

I've worked out a temporary 'bodge' by adding an invisible 4th field to the
report which is generated in excel and cut and pasted over. Requires a bit of
maths but does the trick.

Your way may require less messing about in the long run however.

SQL statement for 8 cards per page and 100 pages:

SELECT 8*([Record Number]-1)+1-799*(Int(([Record Number]-1)/100)) AS SortOrder,
your table name.[Record Number]
FROM your table name
ORDER BY 8*([Record Number]-1)+1-799*(Int(([Record Number]-1)/100));

Just a wizard prodder
Chuck