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

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

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