Query problem (missing records)

[DougP]

I am creating a query with 3 tables<br>
1. Part Master<br>
2. Prices<br>
3. Work Order<br>
Now I can get the query to run but not all of the records are not showing up.<br>
If I remove the Work Order table all of them show up.<br>
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.<br>
I tried the One-Many relationship thingy al three ways but no good.<br>
am I missing something here.<br>
<br>
TIA<br>
DougP<br>

 

[WP]

Doug,<br>
<br>
It doesn't seem likely that you will get the result you are looking for without a little bit of work in between.<br>
<br>
I can think of a couple of solutions:<br>
<br>
1. Create a work table that contains both Part Master and W.O details this eliminating the W.O. join.<br>
<br>
2. Create a dummy W.O. record (Key: 0 if numeric, Key:&quot;None&quot; if text ) on the W.O. table.<br>
<br>
Then create a copy of the Part Master replacing any Null W.O.'s with your dummy key.<br>
<br>
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<br>
<br>
Hope this helps,<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[DougP]

Thanks Bill<br>
Yes there are 12000 records in the parts table.<br>
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.<br>
See they run this report several times during the day<br>
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.<br>
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.<br>
I'll look over your thoughts and merge some of it with mine etc.

 

[elizabeth]

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: <br>
SELECT ... FROM (tblOrder RIGHT JOIN tblPart ON...) INNER JOIN tblPrice ON ... <br>
<br>