Keeping records in order after cutting

[dful]

I need to print 4 records to a page, then after printing, cut the reports in half then quarters.

I then need the 1/4 sheets to be in sequence.
I am thinking I need to reorder the records somehow.

I think the reports need to look like this: (3 pages)

#1 #7


#4 #10


Next Page

#2 #8

#5 #11

Next page

#3 #9

#6 #12


Any Ideas on how to do this.

 

[GingerR]

have you tried setting the COLUMNS in Page Setup?

 

[dful]

Yes.

There are only 2 options

#1 #2 #1 #3


#3 #4 #2 #4

Next Page or Next Page

#5 #6 #5 #7


#7 #8 #6 #8


This will not work when cutting the report into quarters

 

[GingerR]

how about ordering your recordsource to be how you want it in combination with using COLUMNS? what data is in each record? what are you currently sorting on?

 

[dful]

I think I need to explain more.

I am printing postcards with address information 4 to a page. I am using columns in the page setup to get the 4 records on a page.
The addresses are in a zip code order & I need to keep them in sequence. There is a field with the sequence number. My issue is figuring out how to reorder the records so when the actual paper is cut into 1/4 & stacked on top of each other, they are still in this sequence. I am going to be producing 60,000 of these per week.

Thanks.

 

[GingerR]

I understand about stacking them and cutting them. please provide a sample of the data - the order it's currently in and the order you want it to be printed in. How do you determine a 'sequence number'?

is there always only 3 pages? or was that just an example and there will be a various number of records each time you run it?

 

[dful]

Thanks for the reply.

The print sequence number is determined by postal coding software.
There will be various records. up to 60,000.
This is just basic addressing data.(Seq, name, address, city, state, zip)
the ordering needs to be dynamic depending on the number of records.

if I print in sequence like:
#1 #3


#2 #4

Next Page

#5 #7

#6 #8

When I cutting them & stack them they will not be in order.
When I cut them they will be in the following order: 1,5,2,6,3,7,4,8

I am working on segmenting the data into quadrants & reordering the data.
Does that make any sense????

 

[GingerR]

i get it. i'm working on it. it's a real challenge. I've figured it out if the # of records is a multiple of 4, but am struggling for when it's not.

is the "print sequence number" an integer, starting with 1 and incrementing by 1?

when do you need this done by? I'm about out of steam for the night )

 

[GingerR]

ok i did it. might be a little hokey but it works. maybe with the code i wrote it will give you ideas so you can devise something else if you feel like it.

Ok here's the gist: you have to use DAO recordsets so you can count the number of records (so you can figure out how many pages of 4 postcards you'll have), and so you can loop thru the records and calculate a new Order.

You'll have to tweak the code as necessary to fill in your table and field names:

My table is called SEQTest.
I have a field called SeqNo (integer 1 thru the end)
This would be your table you talk about.

I made a second table called SEQTestTemp.
It has a field called SeqNoTemp (integer).
It has a 2nd field called OrderTemp (integer).
The table is blank.

So the code counts the number of records; if it's a multiple of 4, great. If not, it figures out the next higher number that IS a multiple of 4. Long story, but if you don't do this, it honks up the order.

Next, it divides the number of records figured above by 4 to get how many pages of 4 postcards you'll have.

Then it puts a list in the TEMP table: it puts the 'real' Seq Number in the SeqNoTemp field (1 thru whatever), and next to each SeqNoTemp it puts the newly-calculated ReportOrder. I'm not gonna explain how I figured that out cause I have 11 pieces of paper with chicken scratches all over them in front of me...

Anyhow, create a new MODULE and paste in this code, tweaking as necessary:

Function DetermineReportOrder()
    Dim rs, rsTemp As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SeqTest")
    Dim intRecordCount, intPageCount, i, j As Integer
    'Determine Recordcount. If it's not a multiple of 4, make it be the next higher multiple of 4
    'For example if there are only 26 records, make the recordcount we use be 28 (a multiple of 4)
    If rs.RecordCount Mod 4 = 0 Then
        intRecordCount = rs.RecordCount
    Else
        intRecordCount = Int((rs.RecordCount + 4) / 4) * 4
    End If
    
    intPageCount = intRecordCount / 4
    
    CurrentDb.Execute ("Delete * from SeqTestTemp")
    
    Set rs = Nothing
    Set rs = CurrentDb.OpenRecordset("SeqTestTemp")
    
    For j = 1 To intRecordCount
        rs.AddNew
        rs!SeqNoTemp = j
        If ((j - 1) / intPageCount) = Int((j - 1) / intPageCount) Then
                rs!OrderTemp = Int(j / intPageCount) + 1
                i = Int(j / intPageCount) + 1
        Else
            rs!OrderTemp = i + 4
            i = i + 4
        End If
        rs.Update
    Next j
    
    Set rs = Nothing

End Function

Make sure you have a reference to DAO 3.6 set in your REFERENCES.

Run the code (if you don't know how, just put your cursor in front of the "F" in Function and hit the RUN button). It will fill up the TEMP table with a list of Seq Numbers (as if there are a full multiple of 4 of them) and their cooresponding new Order.

Then in your report, go into the RecordSource. Bring in the two tables (your existing one plus the new TEMP one). Make a join between the two tables, on SeqNoTemp and SeqNo, with the arrow pointing to the SeqNo in the pre-existing table. In the query grid, bring down the OrderTemp field and sort ascending on it. Don't bring the 'real' sequence number into the query grid, or if you do, don't sort it in any way. You want the sort to be on the OrderTemp field.

So try that and let me know how it goes.

g

 

[dful]

Awesome, Thanks!!

I found another way to do it.

If you are interested?

I created a query
"SELECT [table1].seqno, [seqno] Mod 4 AS Expr1
FROM table1
WHERE ((([seqno] Mod 4)=1));"


Then append to a temp table (just like yours)with an autonumber field.
This gives me the output like your "seqtesttemp"
seq auton
1 1
5 2
9 3
13 4.......

I repeat this with 3 other queries changing the WHERE statement =2,3, & 0
appending each time to the temp table
I then linked the tables in the report the same way you did.
IT WORKED.
Then I went to post & saw that you figured out another way.
I tried your way & it pretty much worked the last 2 pages were kind of goofy, but easy to fix.

Thank you so much.
What a pain in the a$$!!

 

[GingerR]

it was a good little challenge though!
the way you did it--i tried it but the last two pages came out goofy--funny you said the same thing about mine ) Maybe it was just my interpretation of your instructions...

i had 25 records.
in the upper left are records 1-7
Bottom left = 8-14
Upper right = 15-21
Bottom right = 22-25, then three blanks

Yours:
Upper Left = 1-7 (same as mine)
Bottom Left = 8-13, then one blank
Upper Right = 14-19 and one blank
Bottom Right = 20-25 and one blank

Anyhow, as long as it works! Good Job!

g