Page 1 of 3 123 LastLast
Results 1 to 15 of 38

Report, Calculate pallet qty to fill a truck load, page break and continue to next truck load

  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107

    Report, Calculate pallet qty to fill a truck load, page break and continue to next truck load

    Here's what I'm trying to accomplish unsuccessfully so far:

    My table (or query) contains Item ID's, quantities, due dates, pallet multiples.

    I created a report from the query that shows what to order, when it is due (sorted from sooner to later) and how many pallet each line represents.

    Example

    SKU OrdQty DueDate Multiple #ofPallets

    A123 10,000 06/10/2019 1,000 10
    A123 05,000 06/25/2019 1,000 05
    B456 15,000 06/26/2016 5,000 03
    A123 20,000 07/25/2019 1,000 20

    The total amount of pallets in the example is 38 and 1 full truck is 26 pallets only.

    I want the report to display the first 26 pallets on 1 page then continue on to the next truck load on a subsequent page after splitting the last line accordingly.

    If I needed to order 20 pallets of the last SKU in total but there are only 8 spots lefts in the truck, I want the report to apply 8 pallets worth of the last SKU and start the next page (next truck load) with the balance of that line.

    So my first page should show

    SKU OrdQty DueDate Multiple #ofPallets

    A123 10,000 06/10/2019 1,000 10
    A123 05,000 06/25/2019 1,000 05
    B456 15,000 06/26/2016 5,000 03
    A123 08,000 07/25/2019 1,000 08

    And my second page should show

    SKU OrdQty DueDate Multiple #ofPallets

    A123 12,000 07/25/2019 1,000 12



    So I want to take the truck load manual calculation out of the report and have Access print report blocks of 26 pallets or less but not more.

    Thank you

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,461
    You're not showing if there is a grouping by truck/load or whatever. If it's only based on what you're showing, then not too difficult, I think. You add a textbox (can be hidden) with a record source of =1 and set it to do a running sum (usually over group). In the detail section of the report records, you place a page break control. In that section's format event, you make it visible if the running sum divided by the required value = 0, e.g.
    Me.pageBreakName.Visible = Me.txtBoxName MOD 26 = 0

    If you have a truck grouping, then I have no idea how to shift the remainder to a different truck or load grouping.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by Micron View Post
    You're not showing if there is a grouping by truck/load or whatever. If it's only based on what you're showing, then not too difficult, I think. You add a textbox (can be hidden) with a record source of =1 and set it to do a running sum (usually over group). In the detail section of the report records, you place a page break control. In that section's format event, you make it visible if the running sum divided by the required value = 0, e.g.
    Me.pageBreakName.Visible = Me.txtBoxName MOD 26 = 0

    If you have a truck grouping, then I have no idea how to shift the remainder to a different truck or load grouping.
    Thank you so much for your suggestion.

    There is no grouping at all, it's simply a list of items to be ordered and how many pallets each line represents.
    We order only by multiple of 26 pallets (1 truck load) so I want the page break to happen when the pallet count is 26. The running sum would work if each line was 1 pallet but each line has multiple pallets, splitting the last line is my issue.


    Real life example of my current report:

    item 1 = 01 pal =1
    item 2 = 08 pal =9
    item 3 = 01 pal =10
    item 4 = 02 pal =12

    item 5 = 20 pal = 32 --> I need to insert a break here and split this record

    item 6 = 04 pal = 36
    item 7 = 01 pal = 37

    The first page of the report should end by "item 5 = 14" because 12 + 14 = 26

    and

    the second page should start by its balance as such:

    item 5 = 06 pal = 06

    item 6 = 04 pal = 10
    item 7 = 01 pal = 11

    etc..

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I think you'll have to do this with a temp table, I don't know of a way to split things like this without having some sort of intermediary step.

    Simple Example Enclosed

    If someone else knows how to do it I would be interested in the solution (without code/temp table)


    gotrouvetout67.zip

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I would be fine by using a temporary table, there isn't a lot of data so it would calculate fast but the question is how to get that line split (not manually).

    I'm thinking it might require some VBA programming, which I am not familiar with.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,461
    Sorry, I kinda missed the boat on that one. Seems to me this would be fairly complex - maybe too much for a report. While you could total the pallets as you go and if >26, subtract 26 from the current total and show the page break. You'd also have the remainder that needs to be shifted but at the moment I don't know how I'd force a repeat of the prior item ID in the next record. It might be as simple as some sort of section property like "keep with next" or something but I don't know. I often solve my own report problems but it usually takes a lot of time and effort to overcome the complex ones. You could try it with a logic something like
    IF [sum of pallets] > 26 THEN [item] = [item] - ([sum of pallets] - 26) the caveats being that 1) you only want to affect the current record and 2) you don't want to alter source data, so you can't do this on the bound [item] field.

    Another possible approach is that this gets parsed out into "sections" by vba code by looping through a recordset of the data that makes up your current report: summing as you go and updating a table. You'd get the prior item ID by backing up one record in the recordset and use it to start the next group. The report opens off of this table.

    This is an issue that I would normally be interested in playing with if you provided the db. However, even if you would, I will be undergoing somewhat severe hand surgery tomorrow and while I expect to be minimally involved in forum stuff (just to keep my mind off of the expected pain) I'm not sure how much I could do or even concentrate on for a while, and you probably need a resolution. I would not promise to work on anything that you would upload. Who knows? Maybe I'll be a bit drugged up and wouldn't be able to concentrate. I just don't know what to expect. Hopefully someone else will step in here.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Click image for larger version. 

Name:	Untitled.png 
Views:	31 
Size:	15.3 KB 
ID:	38456

    the report in my database has a page break where you want it, look at it in print preview, not layout. There is a special character available on your ribbon bar for page breaks.

  8. #8
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Yes but how do you generate "tblBaseReport" where itemID = 5 is split?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	30 
Size:	33.2 KB 
ID:	38457

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by Micron View Post
    Sorry, I kinda missed the boat on that one. Seems to me this would be fairly complex - maybe too much for a report. While you could total the pallets as you go and if >26, subtract 26 from the current total and show the page break. You'd also have the remainder that needs to be shifted but at the moment I don't know how I'd force a repeat of the prior item ID in the next record. It might be as simple as some sort of section property like "keep with next" or something but I don't know. I often solve my own report problems but it usually takes a lot of time and effort to overcome the complex ones. You could try it with a logic something like
    IF [sum of pallets] > 26 THEN [item] = [item] - ([sum of pallets] - 26) the caveats being that 1) you only want to affect the current record and 2) you don't want to alter source data, so you can't do this on the bound [item] field.

    Another possible approach is that this gets parsed out into "sections" by vba code by looping through a recordset of the data that makes up your current report: summing as you go and updating a table. You'd get the prior item ID by backing up one record in the recordset and use it to start the next group. The report opens off of this table.

    This is an issue that I would normally be interested in playing with if you provided the db. However, even if you would, I will be undergoing somewhat severe hand surgery tomorrow and while I expect to be minimally involved in forum stuff (just to keep my mind off of the expected pain) I'm not sure how much I could do or even concentrate on for a while, and you probably need a resolution. I would not promise to work on anything that you would upload. Who knows? Maybe I'll be a bit drugged up and wouldn't be able to concentrate. I just don't know what to expect. Hopefully someone else will step in here.
    I was looking into an IF statement as well and indeed some sort of VBA programming to loop though each line until it reaches 26 but I don't know VBA programming yet. Good luck with the hand.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Read the code in the ON CLICK event of the 'view report' button.

    Note, this code will break down if it's possible for an item to fill more than 2 trucks
    i.e. 28 pallets (1 in truck a, 26 in truck b, 1 in truck c)

    Code:
    Private Sub cmdReport_Click()
    Dim rst
    Dim iPalMax, iPalCurr, iQty, iPage As Long
    
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblBase WHERE [ORDERID] = 1 ORDER BY [ItemID]")
    iPalMax = 26
    iPalCurr = 0
    iPage = 1
    CurrentDb.Execute "DELETE * FROM tblBaseReport"
    Do While rst.EOF <> True
        iQty = rst!orderqty
        If iPalCurr + iQty <= iPalMax Then
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iQty & "," & iPage & ")"
            iPalCurr = iPalCurr + iQty
            If iPalCurr = iPalMax Then
                iPage = iPage + 1
                iPalCurr = 0
            End If
        Else
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iPalMax - iPalCurr & "," & iPage & ")"
            iQty = iQty - (iPalMax - iPalCurr)
            iPage = iPage + 1
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iQty & "," & iPage & ")"
            iPalCurr = iQty
        End If
        rst.MoveNext
    Loop
    
    
    DoCmd.OpenReport Replace(Screen.ActiveControl.Name, "cmd", "rpt"), acViewPreview
    End Sub

  11. #11
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Oh, I did not see that indeed, now I have to see how to adapt it to my DB, thank you.
    There are items that occasionally generate more than 26 pallets per line, spreading over more than 2 trucks would randomly depend on the sequence.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    It should not be random, I specifically ordered them so you could reproduce the shipping manifest if necessary. Because the table is flushed and filled every time it will not retain the history. That being said it is easy enough to cycle through the 'remaining' balance until it does work, you'll just have to modify the looping mechanism a little.

  13. #13
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I'll see if I can test it tomorrow. Not sure about the sorting because I sort my records by due date and not by ItemID. Some items will show multiple times because they have different due date but they could end up in the same truck order. I have to sort by due date so the truck order is getting built by what is needed first. For example I can have item 2 needed for 6/7 1 pallet and another pallet needed on 6/14 but they will end up on the same order.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    The sort order does not matter, as long as you can reproduce the same results time after time. The only caveat here is if you intend to keep all your 'manifest' data rather than flushing and refilling it then the sort doesn't matter at all. Then you'll have to remove the delete query from the start of the code to prevent your history from being purged every time you produce a shipping breakdown.

  15. #15
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by rpeare View Post
    The sort order does not matter, as long as you can reproduce the same results time after time. The only caveat here is if you intend to keep all your 'manifest' data rather than flushing and refilling it then the sort doesn't matter at all. Then you'll have to remove the delete query from the start of the code to prevent your history from being purged every time you produce a shipping breakdown.
    I don't need to keep anything, the data is refreshed 3 times a day so it's a moving target. I just need to print that report twice a week, it tells me what I need to order from that particular vendor 10 days ahead of needed on site. Hopefully I can make it work, I'll let you know.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-24-2018, 04:45 PM
  2. Driver Paperwork/Truck DataBase
    By Daisy509th in forum Database Design
    Replies: 2
    Last Post: 03-22-2018, 08:47 AM
  3. Replies: 2
    Last Post: 02-04-2016, 08:13 AM
  4. truck loads and daily totals?
    By nickdixon14 in forum Access
    Replies: 12
    Last Post: 10-13-2015, 05:16 PM
  5. Truck transportation business orders
    By aurorist in forum Database Design
    Replies: 3
    Last Post: 01-12-2012, 05:16 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
  •  
Tech Forums: Microsoft Office Forums