By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Presorted 3-dimensional stacks of cards?

P: n/a
MLH
Suppose you needed to print a large number of consecutively
numbered documents the size of a post-card ==or perhaps even
a business card using a laser printer and 60# bond. Suppose you
planned to print them on a ream of 8.5 by 11 or 14 stock and take
them to the office store for later cutting.

It would be nice if the paper cutter could produce your finished
product - say 4, 8, 12 or more individual stacks of cards, rubber
banded & consecutively numbered top to bottom in each stack.
Sorting records in a manner to achieve the described results is
not something easily described and I'm having some difficulty
coming up with useful & descriptive keywords to use as criteria
to search on.

I'm not sure that the objective is clearly described in the above
two paragraphs. Even if it is, I'm at a loss as to how I might
condense it into a few key words. Anybody?
Dec 3 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
MLH
Wow. Lemme take another stab at this. I just
don't think my first description of the objective
is easily understood. Here's an example:

Say you had to print 100 cards consecutively
numbered 1 to 100. Say you wanted to print the
3x5 card sized documents onto 25-sheets of
8.5x11 paper 4-up on a page. Suppose you
wanted them arranged on the pages so that
you could put your 25-sheet stack of 8.5x11
printouts in a paper cutter, make a few cuts
to trim out your 3x5 cards creating 4-stacks
of cards all containing consecutively ordered
cards. There now. I've said it and I think its
more clear.

Q: How to sort records via query or code to
produce the desired result when fed to a report?
Dec 3 '06 #2

P: n/a
MLH
A smaller example - say 40 cards
being printed 4-up on a page would
result in page 1 having the four
numbers shown in the first row.
The 2nd page would have the
four numbers shown in the 2nd
row and so on...

1 11 21 31
2 12 22 32
3 13 23 33
4 14 24 34
5 15 25 35
6 16 26 36
7 17 27 37
8 18 28 38
9 19 29 39
10 20 30 40

Can SQL be used to sort consecutive
numbers 1-to-40 in a manner that produces
1,11,21,31,2,12,22,32,3,13,23,33...10,20,30,40?
Dec 3 '06 #3

P: n/a
Store those numbers in a text field, and they will sort like you have
shown. Alphanumerically.

Setup a 2-column report, 2 records tall, and you'll be all set.

Jeff Smeker

MLH wrote:
A smaller example - say 40 cards
being printed 4-up on a page would
result in page 1 having the four
numbers shown in the first row.
The 2nd page would have the
four numbers shown in the 2nd
row and so on...

1 11 21 31
2 12 22 32
3 13 23 33
4 14 24 34
5 15 25 35
6 16 26 36
7 17 27 37
8 18 28 38
9 19 29 39
10 20 30 40

Can SQL be used to sort consecutive
numbers 1-to-40 in a manner that produces
1,11,21,31,2,12,22,32,3,13,23,33...10,20,30,40?
Dec 3 '06 #4

P: n/a
well, i came up with a solution, but it takes a bit of doing. if you want to
try it out, suggest you do so on a *copy* of your database. do the
following:

1. create a new table, as

tblSort
ID (primary key)
xSort (Number data type, make sure the DefaultValue property is blank)

the data type of the ID field must be the same as the data type of your data
table (or Long Integer, if the data table has an Autonumber primary key).
i'll call your data table TableA.

2. open a new query in Design view, and add TableA and tblSort. link the two
tables *from* TableA.primarykeyfield *to* tblSort.ID. from the menu bar,
click View | Join Properties and in the dialog box, choose option 2 "Include
ALL records from TableA...". in the query grid, add all the fields you need
from TableA, and the xSort field from tblSort. set whatever criteria you
need to return the records you want in the report. sort Ascending on
whatever field(s) will give you the final order you want for the "cut and
stacked cards". (note that the data records do NOT have to have a
consecutive number field, you can sort by "last name, first name" or "city"
or "zip code" - whatever data fields you choose.) save and close. i'll call
the query qrySortedLabels.

3. base your labels report on qrySortedLabels. in the Sorting and Grouping
dialog, sort Ascending on the xSort field.

4. add the following procedure to a public module, and make sure you do NOT
name the module the same name as the procedure.

Public Sub isSortSetup()

Dim rst As Recordset, i As Integer
Dim num As Integer, cnt As Integer
Dim pages As Integer, labels As Integer

Set rst = CurrentDb.OpenRecordset("qrySortedLabels", _
dbOpenDynaset)

rst.MoveLast
rst.MoveFirst
labels = InputBox("How many labels does each page have?")
If rst.RecordCount Mod labels 0 Then
pages = Int(rst.RecordCount / labels) + 1
Else
pages = rst.RecordCount / labels
End If

i = 1

For i = 1 To labels
num = i
cnt = 0
Do
rst.Edit
rst("xSort") = num
rst.Update
num = num + labels
cnt = cnt + 1
rst.MoveNext
Loop Until cnt = pages _
Or num rst.RecordCount
Next i

rst.Close
Set rst = Nothing

End Sub

the code loops through the query and assigns a sorting number to each
record, with that number stored in tblSort. the code will handle setup for a
report with any number of records and any number of "labels" per page.

5. to print the report, do the following

delete all records from tblSort.
run the isSortSetup procedure, answering the "how many labels per page"
question (and make sure you answer it correctly!).
print the report.

the above solution works, but is clunky - so hopefully somebody else will
offer a more elegant solution, and we'll both learn something!

hth
"MLH" <CR**@NorthState.netwrote in message
news:vv********************************@4ax.com...
A smaller example - say 40 cards
being printed 4-up on a page would
result in page 1 having the four
numbers shown in the first row.
The 2nd page would have the
four numbers shown in the 2nd
row and so on...

1 11 21 31
2 12 22 32
3 13 23 33
4 14 24 34
5 15 25 35
6 16 26 36
7 17 27 37
8 18 28 38
9 19 29 39
10 20 30 40

Can SQL be used to sort consecutive
numbers 1-to-40 in a manner that produces
1,11,21,31,2,12,22,32,3,13,23,33...10,20,30,40?

Dec 3 '06 #5

P: n/a
MLH
Here's the snippet I came up with to arrange 41 records
in table A having values 1-41 consecutively. The arrangement
being, of course, a 2D array. Watch out for word wrap.

'************************************************* *
' OK. We've got a table named A with 41 records I
' wanna print on 11-sheets (4-up on a page) so's I
' can later chop the 11-sheet stack into 4 smaller
' stacks each containing consecutively numbered
' documents.
'
' The first sheet needs to have 1, 11, 21 & 31 on
' it. The second sheet needs to have 2, 12, 22 &
' 32 on it
'************************************************* *
'TheCount 41 elements = the count of all the records that have
to be printed
'PerPage 4 columns = how many I want on a page, IE, 4-up,
15-up, whatever.
'FPages 11 rows =
INT(TheCount/PerPage)+IF(MOD(TheCount,PerPage)>0,1,0)
'EXAMPLE: Table A has 41 records. I want 4-records per page to be
printed on 11-pages
'so I create a 4x11 matrix array (4-columns, 11-rows). The number of
items to be on
'a page is the number of columns in the array. The number of sheetz of
paper its gonna
'take is the number of rows. Each item on a page is a column and each
page is a row.

'1 12 23 34 1 12 23 34
'2 13 24 35 2 13 24 35
'3 14 25 36 3 14 25 36
'4 15 26 37 = 4 15 26 37
'5 16 27 38 ============== = 5 16 27 38
'6 17 28 39 ================ = 6 17 28 39
'7 18 29 40 ============== = 7 18 29 40
'8 19 30 41 = 8 19 30 41
'9 20 31 9 20 31 41
'10 21 32 10 21 32 41
'11 22 33 11 22 33 41

Dim TheCount As Long, PerPage As Long, FPages As Long, iCol As Long,
jRow As Long
Dim MyDB As Database, MySet As Recordset
Set MyDB = CurrentDb
Set MySet = MyDB.OpenRecordset("A")
TheCount = DCount("*", "A")
PerPage = 4
FPages = Int(TheCount / PerPage) + IIf((TheCount Mod PerPage) 0, 1,
0)
ReDim MyArray(PerPage, FPages)
For iCol = 1 To PerPage
For jRow = 1 To FPages
If MySet.EOF Then MySet.MovePrevious
MyArray(iCol, jRow) = MySet!myNum
Debug.Print MyArray(iCol, jRow);
If Not MySet.EOF Then MySet.MoveNext
Next jRow
Next iCol
MySet.Close
MyDB.Close
Set MySet = Nothing
Set MyDB = Nothing

I've not tried your suggestion yet. Will do so now. And yes, this too
is a klugey brute force attack. Would like an elegant SQL replacement.
Dec 3 '06 #6

P: n/a
MLH
Oops... to display the new sort order in the array after closing the
DB and the recordset, tack on another debug.print line and the
for-next loop as shown.

MySet.Close
MyDB.Close
Set MySet = Nothing
Set MyDB = Nothing

Debug.Print
For jRow = 1 To FPages
For iCol = 1 To PerPage
Debug.Print MyArray(iCol, jRow);
Next iCol
Next jRow

At that point, I would be building a table (essentially your
tblSort) to from the array in the order its terms are listed in
the debug window. I guess, Tina, we both constructed the
solution brick by brick.
Dec 3 '06 #7

P: n/a
MLH
I'm not sure I follow you?
Dec 3 '06 #8

P: n/a
MLH
Truth be known, I'd rather have this type of sort.
Watch out for newsreader wordwrap. Cut 'n paste
into a module for legibility and clarity. Or paste it in
notepad using monospaced font of other unkerned
character...

'1 12 23 34 1 12 23 34
1 11 21 31 first page
'2 13 24 35 2 13 24 35
2 12 22 32 second page
'3 14 25 36 3 14 25 36
3 13 23 33 third page
'4 15 26 37 = 4 15 26 37
= 4 14 24 34 fourth page
'5 16 27 38 ============== = 5 16 27 38
============== = 5 15 25 35 fifth page
'6 17 28 39 ================ = 6 17 28 39
====== OR ====== = 6 16 26 36 sixth page
'7 18 29 40 ============== = 7 18 29 40
============== = 7 17 27 37 seventh page
'8 19 30 41 = 8 19 30 41
= 8 18 28 38 eighth page
'9 20 31 9 20 31 41
9 10 29 39 ninth page
'10 21 32 10 21 32 41
10 20 30 40 tenth page
'11 22 33 11 22 33 41
41 eleventh page

The sort patter on the far right is the one I would prefer.
Dec 3 '06 #9

P: n/a
MLH
God! That was hard to read. So am trying again to
put it here in a way that will cut 'n paste legibly. Be
sure to use monospaced font.

Truth be known, I'd rather have this type of sort.
Watch out for newsreader wordwrap. Cut 'n paste
into a module for legibility and clarity. Or paste it in
notepad using monospaced font of other unkerned
character...

1 12 23 34 1 11 21 31 first page
2 13 24 35 2 12 22 32 second page
3 14 25 36 3 13 23 33 third page
4 15 26 37 = 4 24 34 34 fourth page
5 16 27 38 ============== = 5 15 25 35 fifth page
6 17 28 39 ====== OR ====== = 6 16 26 36 sixth page
7 18 29 40 ============== = 7 17 27 37 seventh page
8 19 30 41 = 8 18 28 38 eighth page
9 20 31 41 9 10 29 39 ninth page
10 21 32 41 10 20 30 40 tenth page
11 22 33 41 41 eleventh page

The sort pattern on the far right is the one I would prefer. That
way, I discard 3 blank cards on the last page rather than one
card on the last 3 pages. Anybody getting this or am I just a nut
case?
Dec 3 '06 #10

P: n/a
MLH <CR**@NorthState.netwrote in
news:vi********************************@4ax.com:
Suppose you needed to print a large number of consecutively
numbered documents the size of a post-card ==or perhaps even
a business card using a laser printer and 60# bond. Suppose
you planned to print them on a ream of 8.5 by 11 or 14 stock
and take them to the office store for later cutting.

It would be nice if the paper cutter could produce your
finished product - say 4, 8, 12 or more individual stacks of
cards, rubber banded & consecutively numbered top to bottom in
each stack. Sorting records in a manner to achieve the
described results is not something easily described and I'm
having some difficulty coming up with useful & descriptive
keywords to use as criteria to search on.

I'm not sure that the objective is clearly described in the
above two paragraphs. Even if it is, I'm at a loss as to how I
might condense it into a few key words. Anybody?
Order By (SerialNo-StartingSerialNo) mod NumberOfSheets,
SerialNo

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 3 '06 #11

P: n/a
The sort pattern on the far right is the one I would prefer. That
way, I discard 3 blank cards on the last page rather than one
card on the last 3 pages.
the solution i posted *will* put all the "un-used" cards on the last printed
page, regardless of what the record count vs number-of-labels-per-page count
is.

hth
"MLH" <CR**@NorthState.netwrote in message
news:cc********************************@4ax.com...
God! That was hard to read. So am trying again to
put it here in a way that will cut 'n paste legibly. Be
sure to use monospaced font.

Truth be known, I'd rather have this type of sort.
Watch out for newsreader wordwrap. Cut 'n paste
into a module for legibility and clarity. Or paste it in
notepad using monospaced font of other unkerned
character...

1 12 23 34 1 11 21 31 first page
2 13 24 35 2 12 22 32 second page
3 14 25 36 3 13 23 33 third page
4 15 26 37 = 4 24 34 34 fourth page
5 16 27 38 ============== = 5 15 25 35 fifth page
6 17 28 39 ====== OR ====== = 6 16 26 36 sixth page
7 18 29 40 ============== = 7 17 27 37 seventh page
8 19 30 41 = 8 18 28 38 eighth page
9 20 31 41 9 10 29 39 ninth page
10 21 32 41 10 20 30 40 tenth page
11 22 33 41 41 eleventh page

The sort pattern on the far right is the one I would prefer. That
way, I discard 3 blank cards on the last page rather than one
card on the last 3 pages. Anybody getting this or am I just a nut
case?

Dec 3 '06 #12

P: n/a
MLH
Now that is elegant. Can you explain how that works?
Dec 3 '06 #13

P: n/a

MLH wrote:
A smaller example - say 40 cards
being printed 4-up on a page would
result in page 1 having the four
numbers shown in the first row.
The 2nd page would have the
four numbers shown in the 2nd
row and so on...

1 11 21 31
2 12 22 32
3 13 23 33
4 14 24 34
5 15 25 35
6 16 26 36
7 17 27 37
8 18 28 38
9 19 29 39
10 20 30 40

Can SQL be used to sort consecutive
numbers 1-to-40 in a manner that produces
1,11,21,31,2,12,22,32,3,13,23,33...10,20,30,40?
A javascript solution (using an html table); it should be simple to
translate it to VBA:

var z=new Number();
var c=new Number();
c=Math.ceil(aFiles.length/4);
var links=new String();
for(z=0;z<aFiles.length;z++){
if((z%c)==0){
links+='\n<td style="vertical-align:top" width="25%">';
}
else{
links+='\n<br>';
}
links+='\n<a class="small" href="http://www.ffdba.com/downloads/';
links+=aFiles[z];
links+='.htm">';
links+=aFiles[z];
links+='</a>';
}
document.write(links);

The result can be seen at http://www.ffdba.com

Dec 3 '06 #14

P: n/a
MLH <CR**@NorthState.netwrote in
news:42********************************@4ax.com:
Now that is elegant. Can you explain how that works?
Yes. It's a lot easier to visualize if you temporarily sort om
serial number,

Order By ' This sets the order of the rows in the recordset.

(SerialNo-StartingSerialNo) ' sets up an ordered list starting
at 0. This is required because otherwise the first deck is short
one card. It's also useful if the serial numbers start at
something higher than 1.

(SerialNo-StartingSerialNo) mod NumberOfSheets,

generates a sequence that starts at 0 and runs for the number of
sheets, then starts over. Wnen we sort on this column, it
bubbles all the items that start on page 1 to the top of the
recordset.

SerialNo ' this sorts the items on one page.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 4 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.