Page 3 of 3 FirstFirst 123
Results 31 to 38 of 38
  1. #31
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Here's a sample that recreates the error, just click on the form button, it will open the report table where you can see it bugs out starting line 3 as no record per page should be larger than 26 in [OrderQty]

    TEST_truckload.accdb

    Thank you!

  2. #32
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the code assumes you're never going to have more than 2 trucks holding the same order ID so you have to provide for the case where it's not. you can do that simply by inserting a looping mechanism in the ELSE loop

    Code:
    Do While rst.EOF <> True    iQty = rst!OrderQty
        If iPalCurr + iQty <= iPalMax Then
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iQty & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)"
            iPalCurr = iPalCurr + iQty
            If iPalCurr = iPalMax Then
                iPage = iPage + 1
                iPalCurr = 0
            End If
        Else
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iPalMax - iPalCurr & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)"
            iQty = iQty - (iPalMax - iPalCurr)
            Do While iQty > iPalMax
                iPage = iPage + 1
                CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iPalMax & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)"
                iQty = iQty - iPalMax
            Loop
            iPage = iPage + 1
            CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iQty & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)"
            If iQty = iPalMax Then
                iPage = iPage + 1
                iPalCurr = 0
            End If
            
        End If
        rst.MoveNext
    Loop
    All this does is after you've appended a partial fill on the first truck, check to see if the remaining quantity is greater than the maximum pallet load, if it is, just cycle through until it's not.

  3. #33
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Perfect, thank you so much!

  4. #34
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Actually... this is not quite working yet.

    What happens now is that it is does not always create multiples of 26 pallets when an item spreads over multiple pallets but not more than two pallets.

    I'll post a revised version of the sample later tonight with data that illustrate that issue.

    As you can see below the first pal is ok. Between PageNum 1 and 3, the common item is "item-2" and it causes page 2 to only take 25.
    Similarly "item-1 " is common to page 2 and 3 and causes page 3 to only take 8 pallets.

    When a single item spreads over 2 pages, it skips counting to 26 on the subsequent page containing the remainder of that item spread.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	68.9 KB 
ID:	39746

  5. #35
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Here's a copy with the data set that reproduces this error. TIA.

    TEST_truckload1.accdb

  6. #36
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I went back and looked through this thread.

    There's never an explicit statement that each item needs to be evaluated separately. For instance you may have 3 or 4 instance of a single item totally 5 pallets. Do you just care that the number of pallets of an item are covered for a specific item?

    So let's say you have 2 items in your order

    Item A 5 Pallets
    Item B 21 Pallets
    Item A 11 Pallets
    Item B 2 Pallets

    Truck 1 - 5 pallets Item A, 21 Pallets Item B
    Truck 2 - 11 Pallets Item A, 2 Pallets Item B

    In my code it may process them in the order above

    But you could also evaluate them like

    Item A 16 Pallets
    Item B 23 Pallets

    Truck 1 - 16 Pallets Item A, 10 Pallets Item B
    Truck 2 - 13 Pallets Item B

    if the second method is acceptable I think I would organize the code differently.

  7. #37
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Sorry was was not clear enough.

    I need to fill trucks by order of due date of each item in that order, they always have to be full (except the last one possibly)

    The raw data is sorted by ascending due date so as the code loops down the list, it should split from top to bottom in multiple of 26, no matter what the item is and no truck should be less than 26 pallets but the last one. If an item at the top of the list is 275 pallets then the first 10 trucks will be of that item only and 15 pallets will be on truck 11, the rest filled with the following items down the list.

    For example if my data sequence is as following:

    item A = 58
    item B =15
    item C = 8
    item A = 20

    it would go like

    Truck 1 = (58 >26) then 26 x item A

    Truck 2 = (58-26 > 26) then 26 x item A

    Truck 3 = (58 - 26 - 26 < 26) then 6 x item A + (6 + 15 < 26) then 15 x item B + (6 + 15 +5 =26) then 5 x item C

    Truck 4 = (8-5 <26) then 3 x item C + (3+20 <26) then 20 x item A = 23, list is exhausted end of loop

  8. #38
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    The first code was actually better. It cracked when we had a very large amount on a single item but subsequent loads equaled 26. The second code generates many loads not equal to 26.

Page 3 of 3 FirstFirst 123
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
  •  
Other Forums: Microsoft Office Forums