If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I need help with data from different tables in detail lines
Hi everyone,
I have an inventory with parts coming in (Receipts table) and going out (Issues table) all the time. We want to reduce stowing labor/costs by generating a report that matches incoming parts with outgoing orders so we can move directly from receiving to shipping. This will save labor by bypassing the "stowing to location" and then "Pick from location" processes. The problem is that I want the report to show the Part # information (group header) followed by two columns: left column contains the unfilled order numbers needing that part and the right column contains the receiving location for boxes containing that part. If I join the tables on part number I get a record for each match of course. For example, if I have 4 orders for the part and Receiving has those parts in 6 boxes, the match generates 24 lines. That's what I'm using now but it is very cumbersome. I want to just list the orders on the left and the locations on the right as follows: Item# 1234 24 oz green plastic bottle case Order Qty | Recv Location Qty 00001 3 | 18x234 17 00002 2 | 22x111 3 00003 5 | 22x112 5 00004 1 | 41x012 11 | 41x033 1 | 66x001 7 This will allow us to fill order #1 from locatoin 22x111, order #3 from location 22x112, and orders 2 & 4 from one of the other locations without ever stowing the material in the warehouse, thus saving labor and storage space. Thanks, Jim |
#2
|
|||
|
|||
I need help with data from different tables in detail lines
Try Using a left join from an Item# table to each of the other tables.
-- Build a little, test a little. "Capt James Cook" wrote: Hi everyone, I have an inventory with parts coming in (Receipts table) and going out (Issues table) all the time. We want to reduce stowing labor/costs by generating a report that matches incoming parts with outgoing orders so we can move directly from receiving to shipping. This will save labor by bypassing the "stowing to location" and then "Pick from location" processes. The problem is that I want the report to show the Part # information (group header) followed by two columns: left column contains the unfilled order numbers needing that part and the right column contains the receiving location for boxes containing that part. If I join the tables on part number I get a record for each match of course. For example, if I have 4 orders for the part and Receiving has those parts in 6 boxes, the match generates 24 lines. That's what I'm using now but it is very cumbersome. I want to just list the orders on the left and the locations on the right as follows: Item# 1234 24 oz green plastic bottle case Order Qty | Recv Location Qty 00001 3 | 18x234 17 00002 2 | 22x111 3 00003 5 | 22x112 5 00004 1 | 41x012 11 | 41x033 1 | 66x001 7 This will allow us to fill order #1 from locatoin 22x111, order #3 from location 22x112, and orders 2 & 4 from one of the other locations without ever stowing the material in the warehouse, thus saving labor and storage space. Thanks, Jim |
#3
|
|||
|
|||
I need help with data from different tables in detail lines
I would create a main report based on the unique parts. Then use two
side-by-side subreports for the Receipts and Issues. -- Duane Hookom Microsoft Access MVP "Capt James Cook" wrote: Hi everyone, I have an inventory with parts coming in (Receipts table) and going out (Issues table) all the time. We want to reduce stowing labor/costs by generating a report that matches incoming parts with outgoing orders so we can move directly from receiving to shipping. This will save labor by bypassing the "stowing to location" and then "Pick from location" processes. The problem is that I want the report to show the Part # information (group header) followed by two columns: left column contains the unfilled order numbers needing that part and the right column contains the receiving location for boxes containing that part. If I join the tables on part number I get a record for each match of course. For example, if I have 4 orders for the part and Receiving has those parts in 6 boxes, the match generates 24 lines. That's what I'm using now but it is very cumbersome. I want to just list the orders on the left and the locations on the right as follows: Item# 1234 24 oz green plastic bottle case Order Qty | Recv Location Qty 00001 3 | 18x234 17 00002 2 | 22x111 3 00003 5 | 22x112 5 00004 1 | 41x012 11 | 41x033 1 | 66x001 7 This will allow us to fill order #1 from locatoin 22x111, order #3 from location 22x112, and orders 2 & 4 from one of the other locations without ever stowing the material in the warehouse, thus saving labor and storage space. Thanks, Jim |
Thread Tools | |
Display Modes | |
|
|