combining two tables without a clear key

[matthewemiclea]

I can't figure out how to combine these two tables that I have:

I have two tables:

Table #1(simplified): Fields: Sales Order, Due Date, Work Order
Table #2(simplified): Fields: Work Order, Component, Qty

I would like to have a table that includes S.O., Due Date, W.O.,
Component, Qty

The issue is that for each Sales Order in table one, I may have
multiple work orders. For each work order then in Table #2, I have
multiple components on each work order. So I created a select DISTINCT
query by tying work order in each table, saying to include all records
in Table #2 and each record in Table #1 where Work Order is the same.

HOWEVER, there are also records in table 1 that have a Sales Order and
Due Date, but have no Work Order tied to it. In this case, I would
like to return these records in the query, but with all of the fields
in table #2 left blank.

Anyone have any ideas??? Would be GREATLY appreciated!

Thanks,

Matt

 

[Rob Parker]

I think that all you need is to set the join (between the Work Order fields
in each table) to show all records in Table1 and only those records from
Table2 where the joined fields are equal. No need for any DISTINCT keyword.
If that doesn't give what you want, please repost with a better description
(and perhaps some sample data).

HTH,

Rob

 

[matthewemiclea]

Thanks, It looks like that worked. I still needed to add "DISTINCT" in
my code though. When I did not add that, I got duplicate values for
every record.

Matt