Results 1 to 12 of 12
  1. #1
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8

    Grouping a 2 column report by store going from left to right

    Greetings,

    I have been puzzled on this for quite some time and in need of some assistance from the experts. I am looking to create a load log report based on a query that I have created, that shows the location of a specific pallet on a trailer. When we load a trailer, we always load from left to right in 2 columns. Say I have 4 stores total; J111, J121, J131, and J141. We load 3 pallets on the left of the trailer going to store J111. We then load 2 pallets on the right going to store J121. After that we load 2 pallets on the left going to J131, then 3 pallets on the right going to store J141. When you look at the load log, it would look as follows

    Left Right


    J111 J121
    J111 J121
    J111 J141
    J131 J141
    J131 J141

    My question is, how do I reflect this on an Access report? My report is sorted by time stamp and grouped by store, but when I run the report, it is always out of that particular sequence.

    I look forward to hearing from you all!

    Bishop2ya

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) How do you store the load records on the database?

    2) How long is the truck (how many pallets can it take in each column)?

    3) What happens if you needed to send three pallets to the third store (J131) in that example? Does the third pallet end up in location R3? Is the third pallet broken into an entry of its own? Or does the quartermaster hold store J131 until you load two pallets for some other store in the left positions?

  3. #3
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    1. The load records are stored on a table. They are all listed in order pased on the time it was loaded.
    2. A general truck is 56' and holds about 22 pallets.
    3. If the third store has 3 pallets, then all three would be loaded on the same side. All of the stores have to be grouped together on one side or the other. Any more then 22, and the pallets are sent the next week. Generally there is no more than 5 pallets to a single store at one time. Thank you for looking in on this for me!

  4. #4
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    Sorry, question 2. 11 on each side

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) I mean, specifically, what is the layout? Give me the record with the truck id, the number of pallets, the store id, and so on.

    2-3) Good, that makes it easy.

    4) I assume you are just looking for a report to give a quick visual indication of the layout of the truck. Are you looking for one truck per page, or what?

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    5) Are there only ever 4 stores in a truck? In other words, suppose J111 and J131 each got 5 pallets, and J121 and J141 got 2 each, might J151 be loaded on the right?

  7. #7
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    Usually 4 stores, no more than 5

  8. #8
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    Door#\Store\Container\LoadTime\ProductType

    We have 12 doors, we use a container system for each pallet, i.e. pallet 1 555J12 pallet 2 555J13, etc. Load time is the time the pallet was loaded. Product type is what is on the pallet (fruit, donuts, specials, etc)

  9. #9
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    Our load log is one page, I'd like to keep it that way if possible. This helps the drivers know which pallets (and how many) go to what stores.

  10. #10
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    So it's one page per truck, right? Is the Door# the key to identifying the truck/container? If you give me the actual layouts of the tables, I can do the sample SQL much quicker.

    This seems like an appropriate place for a tricky crosstab.

    Here's the high concept:

    You create a table that looks like this:
    Code:
    tblTruckLayout
      Slot    Number  (1 thru 22)
      Side    Text    (L or R)
      Pad     Number  (1 thru 11)     
      Remain  Number  (0 thru 10 - number of pads left behind this slot)
    You fill it with this static data. (finish the obvious pattern.) The reason for including the "Remain" column is that it will help to code tests for if the fifth set of pallets rolled to the right.
    Code:
     SLOT SIDE  PAD  REMAIN
       1    L     1   10
       2    L     2    9
       3    L     3    8
       4    L     4    7
       5    L     5    6
     ....  ...  ....  ...
      10    L    10    1
      11    L    11    0
      12    R     1   10 
      13    R     2    9
      14    R     3    8
     ....  ...  ....  ...
      21    R    10    1
      22    R    11    0
    You create a query that makes this layout, or a VBA routine that loads a temp table with this layout:
    Code:
    qryTruckLoad
       TruckID   Text (Truck name or Key)
       Slot      Number (1 to 22)
       Contents  Text (Store name or Store name and pallet data)
    I can help you figure out how to do that, but I'd need the actual layouts to produce anything workable. Ideally, if you post a set of fifteen to twenty pallets worth of sample data for one Door# on one morning, then I can get pretty close.

    Then you use a crosstab query like this to produce your report:
    Code:
    TRANSFORM First(Contents) As TheContents
    SELECT TruckID, Pad
    FROM
        tblTruckLayout AS T1
        INNER JOIN 
        qryTruckLoad AS Q1
        on Q1.Slot = T1.Slot
    PIVOT T1.Side;

  11. #11
    Bishop2ya is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2009
    Location
    Georgia
    Posts
    8
    I don't have my work computer in front of me at this time. Do you mind if I send you a layout in the morning? Yes, it is one page per trailer, and the identifier is door

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    A field in table (if it can't be generated by query) that identifies the pallet is on left or right. Then a multi-column report has sorting order by that field and column break when the field value changes. Now if I can remember how to do that. Or is it just setting a group on the left/right field and setting ForceNewPage property to After Section?
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-29-2013, 02:16 PM
  2. Left Hand column in chart empty
    By Kirsti in forum Reports
    Replies: 10
    Last Post: 03-20-2012, 01:41 PM
  3. Multiple Left Joins From Same Column
    By x0200196 in forum Access
    Replies: 1
    Last Post: 09-08-2011, 10:14 AM
  4. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  5. Replies: 9
    Last Post: 01-28-2011, 06:05 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