S
shadowsong
i have three tables: order entry, order receipt, and an part number
cross reference table. part numbers are related across all three
tables, as are order numbers. i would like to find the complete order
history (all orders entered and all orders received) for a given part
number.
i can find all records for that part number that match across both
tables (ie, all orders that have been both entered and received), or
all records for the part number on one table (either orders entered OR
orders received, but not both). what i need is all matching records on
the order entry table, plus any records associated with them on the
order receipt table.
the results would then look like this:
PART NUM ONUM OQTY ORDERDATE INVNUM INVQTY INVOICEDATE
8210017822 2847C 700 1/16/2006 5145 700 3/16/2006
8210017822 2907C 300 3/20/2006 5198 300 5/20/2006
8210017822 2938C 300 4/25/2006
8210017822 3009C 300 8/4/2006
(sorry, it lines up better in courier)
part number and order number are found on both tables, order qty and
date are only found on order entry, and the last three invoice columns
are only found on the order receipt table.
so 4 records match the search on the order entry table, and two records
on the order receipt table are linked to the records returned on the
order entry table by way of the part number and order number fields.
how do i do this?
cross reference table. part numbers are related across all three
tables, as are order numbers. i would like to find the complete order
history (all orders entered and all orders received) for a given part
number.
i can find all records for that part number that match across both
tables (ie, all orders that have been both entered and received), or
all records for the part number on one table (either orders entered OR
orders received, but not both). what i need is all matching records on
the order entry table, plus any records associated with them on the
order receipt table.
the results would then look like this:
PART NUM ONUM OQTY ORDERDATE INVNUM INVQTY INVOICEDATE
8210017822 2847C 700 1/16/2006 5145 700 3/16/2006
8210017822 2907C 300 3/20/2006 5198 300 5/20/2006
8210017822 2938C 300 4/25/2006
8210017822 3009C 300 8/4/2006
(sorry, it lines up better in courier)
part number and order number are found on both tables, order qty and
date are only found on order entry, and the last three invoice columns
are only found on the order receipt table.
so 4 records match the search on the order entry table, and two records
on the order receipt table are linked to the records returned on the
order entry table by way of the part number and order number fields.
how do i do this?