I have a database very similiar to the Order Entry template (also similiar to Northwinds sample). However, in the subform where a person can select the parts they want to order, I want to show ALL the parts available - i.e. a list of those that are related to this particular order and parts that are NOT related to this particular order. The first part I can do but I can't figure out how to write the query to get me all the Parts that are NOT related to this Order. (Parts to OrderItem is one-to-many).
Can anyone help?

Further information: I'm very close but probably going about this the wrong way. I am first doing the following query:

QAllParts-Items:
SELECT TblParts.*, TblItems.*
FROM TblParts LEFT JOIN TblItems ON TblParts.PIndex = TblItems.[I-PIndex];

This of course returns all the Parts. If that Part has been ordered multiple times, I get multiple records - e.g. if 4 people ordered it, I get four records. If it has never been ordered, I get one record with nothing in the TblItems fields. This query would be perfect if I could get it to return a record for each order AND a "null" record! E.G. 5 records - four for the people who ordered it please another one. This is because I then do the following query:


SELECT [QAllParts-Items].[I-QQuoteNum], [QAllParts-Items].*
FROM [QAllParts-Items]
WHERE ((([QAllParts-Items].[I-QQuoteNum])=[forms]![frmquote]![qquotenum] Or ([QAllParts-Items].[I-QQuoteNum]) Is Null));


Like I said, this is ALMOST what I want. The problem is if someone else has ordered the Part, it gets eliminated because I don't have a record from the first query where the QQuoteNum is null.

I really don't know SQL so this has been a trial and error so don't be afraid to tell me I'm going about this completely wrong!!

I'm quite confused. If you say:
I want to show ALL the parts available - i.e. a list of those that are related to this particular order and parts that are NOT related to this particular order.
If you want to show all the parts, then why not write a query choosing all the parts, not just the ones related to the order. You say you've got the first part working - there must be some critieria in there that relates the records to your particular order. Can't you just remove that critieria and therefore get everything?

Maybe if I explain what my form is trying to do -
We produce quotations for customers of various parts. So we start with the Customer and then add quotes. TblsQuote is linked to TblParts via TblItems. In the subform of the form for the customer, I want to display all available parts and choose the qty to order and store the TblItem record. The Order Entry template does almost exactly what I'm doing except it links to the subform on the QuoteNum and used a combo box for parts. I want to see the list of parts - not have them in combo boxes like all the examples I've seen. I've attached a jpg if that makes more sense. The problem is that sometime I am MODIFYING an existing quotation so some parts are related to the Quote already. Does that make more sense? I need the Parts that aren't in the quote as well as the ones that are.

No help out there?? I have managed to get the records I want by doing two queries and using a UNION but now I'm faced with having to write lots of code to insert new records when we want to change the quantity. But I'll go down this route if no one has a better solution!

I think the reason why you're not receiving more response is that your explanation is too difficult to follow.I've looked it over several times and I still can't be sure what you're trying to do. It would be better if you posted a sample db, pointing out where it's going pear-shaped, so we can at least have a practical example to work from.