Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9

    Palletisation Report

    Hi All,



    I need to create a simple report to print off and stick to a pallet. It'll have a Title, delivery date, address and job title.

    Where I'm falling down is the page needs to state Pallet 1 of …, 2 of … and so on. The amount of pallets will change every time so can't just hardwire it. I assumed it would just involve the Pages function but I don't know how to force a certain amount of pages.

    Also the quantity of items needs to stated too. So for example Pallets 1 - 25 will contain 15 items of 380 but pallet 26 will only contain 5. Again this changes with every job so the next job may be 4 equal pallets of 20 for a total of 80.

    Let me know if I've explained that terribly. Basically need a force a certain amount of pages and a way of displaying how many items are on each pallet.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,275
    You would need a pallet# group.
    @ pallet would have Contents count.

    break the page on the pallet#, use page# as pallette N of Total

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    You may get some ideas from these threads:
    I searched the forum with create labels for pallets shipment

    https://www.accessforums.net/showthread.php?t=15590
    https://www.accessforums.net/showthread.php?t=70424

    However, if you could describe the process and the rules/constraints in more detail and/or provided an example or 2 using "typical data", I'm sure the forum could offer more assistance.

    How big are the pallets? How many items per pallet? Is it always the same items?
    you haven't provided much info for any testing or calculations.

    I assisted a forum user years ago who was putting items in boxes. It may give some ideas.

    You may also get some idea for approach by reviewing a few articles or youtube videos on bin packing.


    Bin packing videos:
    https://www.youtube.com/watch?v=vUxhAmfXs2o
    https://www.youtube.com/watch?v=B2P1TzKKWOI
    Last edited by orange; 06-02-2020 at 06:12 AM. Reason: spelling

  4. #4
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Thanks for the help both, i'll try to flesh it out a bit.

    We'll recieve an order for a certain amount of stock. This will change everytime and is manually entered by the user as [Quantity].

    Production will then work out how many can fit on a wooden pallet. Also manually entered as [PalletQty].

    I would like for the system to work out how pallets are needed for delivery. This is easy, for example. If the [PalletQty] = 15 and [Quantity] = 380 we would need 26 Pallets. 25 equal pallets of 15 and 1 remaining pallet of 5.

    What I need help with is after [Quantity] and [PalletQty] are entered I would like to click a button that's creates a report consisting of 26 near identical pages.

    The only difference between them being the pallet number ie Pallet 1 of 26, Pallet 2 of 26 and so on AND the [PalletQty] for that pallet ie the first 25 pages will say 15 and the last page will say 5.

    Below is an example of a what I need which was manually created in Word.

    Click image for larger version. 

Name:	Example.jpg 
Views:	16 
Size:	72.4 KB 
ID:	42058

  5. #5
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Just to mention the [Quantity] and [PalletQty] change everytime so the amount of pallets change every time.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Pallets will have a certain size. So they may have a maximum Area (sq ft) or maximum volume (cu ft) representing the Capacity of a pallet. In some cases there may be a weight limit for a pallet, but that doesn't appear to apply to your question/set up.

    Where does PalletQty come from? That's the Capacity I mentioned above.

    Total number of Items = 380
    PalletCapacity= 25

    Required number of pallets = iif(380 mod 25 = 0, 389/25,380\25 +1)
    ' if 380 (Qty) is divisible by 15 (Capacity) evenly (that is 0 remainder) then 380/15
    'if it is not evenly divisible, then integer divide Qty\Capacity +1. The +1 is to handle the partial pallet.

    So your general formula would be

    PalletsRequired =iif (TotalQty mod Capacity=0,TotalQty/Capacity, TotalQty\Capacity +1)

    You could make this a function:

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: PalletsRequired
    ' Purpose: to determine the number of pallets required for a shipment
    ' Procedure Kind: Function
    ' Procedure Access: Public
    ' Parameter lTotalQuantity (Long): Total quantity of Items to Ship
    ' Parameter lCapacity (Long): Pallet capacity
    ' Return Type: Integer
    ' Author: Jack
    ' Date: 02-Jun-20
    ' ----------------------------------------------------------------
    Function PalletsRequired(lTotalQuantity As Long, lCapacity As Long) As Integer
        On Error GoTo PalletsRequired_Error
    If lTotalQuantity > 0 And lCapacity > 0 Then
       PalletsRequired = IIf(lTotalQuantity Mod lCapacity = 0, lTotalQuantity / lCapacity, lTotalQuantity \ lCapacity + 1)
    Else
       PalletsRequired = 9999 'to indicate an error in supplied parameters
    End If
        
        On Error GoTo 0
    PalletsRequired_Exit:
        Exit Function
    
    PalletsRequired_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PalletsRequired, line " & Erl & "."
        GoTo PalletsRequired_Exit
    End Function

    sample usage:

    ?palletsrequired(380,-8)
    9999

    ?palletsrequired(380,15)
    26
    Good luck.

  7. #7
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Hi Orange,

    Thanks for your help.

    The [PalletQty] is worked out by production and doesn't need calculating.

    I'm quite happy working out how many pallets are needed. What I need help with is forcing a report to create a certain amount of pages.

    In the example I gave the user would click on a button that would create a report consisting of 26 pages. Each page would detail that 15 items are on it until you get to Page 26 of 26 which would say Pallet 26 of 26 and there are 5 items on it.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    The [PalletQty] is worked out by production and doesn't need calculating.
    Yes, I suspected that. The function I suggested would use the PalletQty and the TotalNumber of Items and tell you how many pallets were required.It could also tell you how many full pallets and how many items on the last pallet.

    What is the Table(s) where you get the address info, the Delivery Date and the Contents for the report?


    Can you tell us more about the Pages? What exactly is printed/reported on the page?
    Please provide a sample of your data sufficient to produce a few pages to test.

  9. #9
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Hi Orange,

    I have already posted a picture of what I need the finished report to look like.

    Our Production team are currently doing it on Word. Filling it out, printing one page changing the wording from Pallet 1 of 26 to Pallet 2 of 26 and so on. I just need a report to replicate this so that the team doesn’t have to rewrite the document 26 times or however many pallets the job requires.

    The final page may or not have a different quantity than the others.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Where would you get the info if you are doing this in Access? You must have tables for Address??
    Also, the contents of the pallet come from somewhere --an Order??

    Could a pallet contain say 4 lbs of Blue Cheese, 6 loaves of Rye bread; and a 6 pack?? What are the typical contents of a pallet?
    Who determines Delivery Date? From what/where?

    Details please.
    I'm willing to help, but need some realistic data to work with.


    Who ever is doing this in Word is making do with what they have. If you are designing a database to help automate the processing, you have to do some analysis and design. And for efficiency you'll need some test data and test scenarios.

    After thought: If you have and do use Word, you may be able to do a Access/Word merge. Or you could create a template and have place holders for your data values. Then populate the place holders from a query or table??

    DeliveryDate
    Address
    Contents
    Pallet Number Of PalletTotalNumber

  11. #11
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Hi Orange,

    I was planning to base the report on a query to drag all the necessary info in. Below is a screen shot of the query.

    We are a packaging company so a pallet can contain any amount of items that's why it is manually entered. It can't be calculated, it'll literally be a worker fitting as many boxes on as possible, noting down how many they fit on and then putting that many on the next pallet.

    One job could have 100 small boxes the next could have 5 massive ones.

    The delivery date is also manually entered by the user as it is determined by the scale of the job.

    The "contents" of the pallet is just the [Quantity] "x" [Reference] of the job.

    This process hasn't been implemented yet that's why [PalletQty] isn't listed on there yet.

  12. #12
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Below is the query.

    Click image for larger version. 

Name:	qryReport.JPG 
Views:	11 
Size:	101.5 KB 
ID:	42066

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    -We are a packaging company
    -a pallet can contain any amount of items (# of Items per pallet may differ with different Jobs)
    -
    It can't be calculated ( see bin packing info posted in post #3)
    -it'll literally be a worker fitting as many boxes on as possible, noting down how many they fit on and then putting that many on the next pallet.(that's how you are doing it now)

    I'd like some info/real data values from your current setup in order to test a few things.

    Your relationships show tables in 1 to 1 arrangement. Can you give readers a description of your "Packaging Business" so we can understand your business rules/facts - which determine relationships. Please use plain English - no jargon - and lead us through a Job from start to finish. You can keep it generic and high level so we can understand what you do and the flow of work.

    We can help with design, but we need info that only you know at the moment. Your tables are not normalized and that will cause issues as you proceed.

    Research Normalization. For additional info see the Database Planning and Design link in my signature.

  14. #14
    lmjconvey89 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    9
    Hi Orange,

    I've worked out a way to achieve what I needed to do, thanks for the help though.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    lmjconvey89,

    The forum serves a wide and varied audience. Perhaps you could describe or show readers your solution in keeping with the intent of the forum. It could be beneficial to others.
    Glad you have it resolved.

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

Similar Threads

  1. Replies: 16
    Last Post: 03-13-2018, 05:24 PM
  2. Replies: 9
    Last Post: 07-03-2017, 11:24 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 - Senior Forums