Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    mize91 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    4

    Select records for printing 3-up Labels in Stacked order

    Hello,



    I need to print records from a database that are 3-up labels. I would like to put the database in order that after print
    all you need to do is cut and stack each pile in order.

    for example - if the database has 1500 records, I like the order of the database to be 1, 501, 1001, 2, 502, 1002, 3, 503, 1003, etc

    Regards

    mize91

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The report fills 1 page at a time:
    123
    456
    789
    etc

    or
    147
    258
    369

    So why can't you use the left-to-right method,
    and peel stickers that way?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Then you will need another field for a sort order value.

    What logic determines that order? Ahh - First digit from the right?

    Extract that value with: Right([fieldname],1).

    Include that calculated field in sorting criteria.

    But what happens when the tens column rolls over to 1, etc.? How high will these series go?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    mize91 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    4
    Quote Originally Posted by ranman256 View Post
    The report fills 1 page at a time:
    123
    456
    789
    etc

    or
    147
    258
    369

    So why can't you use the left-to-right method,
    and peel stickers that way?

    Thanks for the quick response. I'm not using labels per say. It's a 8.5 x 11 sheet that that's going to be cut 3-up. Each piece will be matched to another 1 page piece I'm printing.
    This is way I'd like to keep the database for the 3-up sheet in an order that after I cut and divide, all I need to do is stack each pile and it's in order to match against other 1 page piece.
    The database actually has 1844 records. I'd like the database in 3-up order. First sheet should have records # 1, 615, 1230 / second sheet should have 2, 616, 1231. After all is printed, I can cut and stack each pile
    and it will be in original order of 1,2,3, etc

    I know a little bit about queries but this should be a macro ( I believe ) cause it needs to loop in the database to read all records and put in the order I would like.

    I hope this clears things up

    Thanks

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    And what data will be used to determine these groups and order? Even if code (VBA not macro) is used, there needs to be data the logic can be based on.

    I have a procedure that sends duplicate 'label' records to a temp table (table is permanent, records are temporary) to print a variable quantity. Order is based on a record indentifier (SampleNum).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I hope this clears things up
    Not at all. Why is record 2 on the second sheet? What record is below record 1 on the first sheet? What's the last record in column 1 on the first sheet?
    You say 3 up, but how many rows? Can you show us an image of a sheet
    with all record numbers filled out? I just don't get the picture you describe.

  7. #7
    mize91 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    4
    Quote Originally Posted by davegri View Post
    Not at all. Why is record 2 on the second sheet? What record is below record 1 on the first sheet? What's the last record in column 1 on the first sheet?
    You say 3 up, but how many rows? Can you show us an image of a sheet
    with all record numbers filled out? I just don't get the picture you describe.

    Original database has an ID column 1 through 1845 in ASC order ( I can use this for printing single page letter )
    I need to match that database to print a 3-up sheet which I will be cutting into 3 piles. Pile 1 will have ID 1-615, pile 2 will have ID 616-1230, and pile 3 will have 1231-1845.

    Original database has address records in order of ID 1,2,3,4,5,etc
    I'm looking to manipulate that data into a new database that will order the ID records as 1, 616, 1231, 2, 617, 1232......last 3 records will be 615, 1230, 1845.

    Is there a program that can sort every 615th record ( starting @ ID 1 ) and keep looping until all records are taken in that order.

    I attached a sample of tags we print off for mailings that are in stack formation ( from sorting software we use )
    Total of 10 tags. sheet one has C#1, C#4, C#7, C#10, sheet two C#2, C#5, C#8, blank, and so on.
    When I cut the tags, I'm able to stack each pile in order of 1-10

    ThanksSKMBT_C55418080916460.pdf

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Still don't get it. AGAIN, can you show a diagram of every label on ONE COMPLETE SHEET with just the ID numbers, 3 UP.
    Not the completed labels, just the ID numbers for those labels.

  9. #9
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Ahh, I see what you mean. So you are printing three labels per sheet, but with the data sorted as it is, the first sheet would have records 1,2,3 the second: 4,5,6 etc, but this isn't what you need because when you cut the labels up, you need the resulting stacks to be in numerical order. Hence needing 1, 616, 1231, 2, 617, 1232 etc. There must be a way to do this. Whenever I have something tricky, I always resort to VBA. I would have thought. Create a second temporary table with the same fields. In VBA get a recordset of all the data and loop through it. Have three counters starting at the records you want which increment every loop and use these to define which records to copy into the new table. Copy a record at a time for the new table has the data in the correct order. If you need to reserve the original ID numbers, you'll need an additional OriginalID column. Base the report that generates the labels off this new otherwise sorted table. It might not be very efficient but it should work.

  10. #10
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Or as June said above, you could avoid the additional table by having a SortOrder field. Then you could just loop through the recordset populating this in turn. This would be much more efficient. Again you would need three counters that you increment each by one every loop. You could calculate the starting values of these counters based on the number of records. I would post some pseudo code but I really should be doing my job (this is more fun on a Friday afternoon though)

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Well, there's the confusion. 3-UP means 3 ACROSS on the sheet in 3 columns; not three on the sheet in one column.

  12. #12
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    With just using a Sort Order field, you'd still be stuck on the last and penultimate page. The report wouldn't be able to cope with the #blank in this example "C#1, C#4, C#7, C#10, sheet two C#2, C#5, C#8, blank, and so on." so you would have to make sure your number of records was exactly divisible by the number of labels per page. You could do this by creating dummy records to fill in the gaps. This would only be a problem for the last 1 or two pages of the stack.

  13. #13
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Despite the name of the function this has not been tested, and I've just written comments for the adjusting the record count section, but something like this:

    Sub test()

    Dim rs As dao.Recordset
    Dim labelsPerSheet As Integer
    Dim countA As Integer
    Dim countB As Integer
    Dim countC As Integer

    ' Get all labelsSet rs = CurrentDb.OpenRecordset("SELECT * FROM tblLabels")

    labelsPerSheet = 3

    ' Get the number of records
    nbrecords = rs.RecordCount

    ' Check if the recordset is directly divisble by labelspersheet
    If nbrecords Mod labelsPerSheet 0 Then
    ' Create a new dummy record
    ' Keep track of it's ID so you can delete it later
    ' Check again
    ' If still not zero repeat
    ' Keep track of ID again
    ' Reget the recordset to include the nnew records
    ' Ensure variable nbRecords is updated
    End If

    countA = 1
    countB = nbrecords / labelpersheet + 1
    countC = (2 * nbrecords / labelpersheet) + 1

    ' Loop through the recordset populating the sort order field
    rs.MoveFirst

    Do Until rs.EOF
    rs("SortOrder") = countA
    countA = countA + 1
    rs.MoveNext

    rs("SortOrder") = countB
    countB = countB + 1
    rs.MoveNext

    rs("SortOrder") = countC
    countC = countC + 1
    rs.MoveNext
    Loop

    ' Delete the temporary records if any that you created earlier

    Rs.CloseSet
    rs = Nothing

    End Sub
    Last edited by Robyn_P; 08-10-2018 at 09:14 AM. Reason: Formatting

  14. #14
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Oh and I missed the rs.edit and rs.update steps. Know it looks a mess, work computer doesn't believe in carriage returns

  15. #15
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Still not ideal formatting, phone doesn't believe in code tags or leading spaces, but better. As said, not tested and you'll need to do a little thinking about the required temporary records and making sure you delete them at the end. Code above certainly won't work due to missing edit/update steps. But off the top of my head I think that should do it for you. Right I'm off. Kitty needs to go to the vet and this really isn't what I am paid to do (fun though!)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Printing labels from order data in Access ERP
    By beachbumch in forum Programming
    Replies: 2
    Last Post: 11-30-2016, 03:05 PM
  2. Code for Printing Labels
    By bezoomnyveshch in forum Access
    Replies: 1
    Last Post: 12-15-2015, 01:21 PM
  3. Replies: 6
    Last Post: 07-28-2014, 01:04 PM
  4. Replies: 1
    Last Post: 05-04-2012, 02:22 PM
  5. Printing Labels.
    By Robeen in forum Reports
    Replies: 1
    Last Post: 05-02-2011, 05:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums