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?
