I am creating a query with 3 tables
1. Part Master
2. Prices
3. Work Order
Now I can get the query to run but not all of the records are not showing up.
If I remove the Work Order table all of them show up.
My problem is not all of the items on my list have a Work Order yet. So when I add the Work Order table it only matches if there is one, eliminating others.
I tried the One-Many relationship thingy al three ways but no good.
am I missing something here.

TIA
DougP

Doug,

It doesn't seem likely that you will get the result you are looking for without a little bit of work in between.

I can think of a couple of solutions:

1. Create a work table that contains both Part Master and W.O details this eliminating the W.O. join.

2. Create a dummy W.O. record (Key: 0 if numeric, Key:"None" if text ) on the W.O. table.

Then create a copy of the Part Master replacing any Null W.O.'s with your dummy key.

If the Part Master is huge then you could make the W.O. the dummy keyon addition to the table and do a one of update of the existing null W.O.'s

Hope this helps,

WP

Bill Paton
wpaton@neptune400.co.uk
www.neptune400.co.uk

Thanks Bill
Yes there are 12000 records in the parts table.
It is slow when I create an extra query so I have a query within a query. Kind of the same result you suggested with multiple tables.
See they run this report several times during the day
It gives them a list of what they are out of and if its been orderd or not. If its on order then when will it arrive.
One reason it's slow it that the parts on order are not a key field. I don't have any control over this because its an ODBC linked table.
I'll look over your thoughts and merge some of it with mine etc.

Are you trying to get all part numbers and prices, and leaving work orders as a blank if none? You would need something like this:
SELECT ... FROM (tblOrder RIGHT JOIN tblPart ON...) INNER JOIN tblPrice ON ...