JOIN error

[faca]

Hello!

I am trying to create query with LEFT join but when i try to save i get error "JOIN is not supported" if i use INNER than it works.

SELECT Parts.PartID, Parts.PartNumber, Parts.PartCode, Parts.PartName, DeliveryDocuments.DeliveryNumber

FROM  Parts LEFT JOIN (DeliveryDocuments LEFT JOIN DeliveryDocumentsParts ON  DeliveryDocuments.DeliveryDocumentsID =  DeliveryDocumentsParts.DeliveryDocumentsID) ON Parts.PartID =  DeliveryDocumentsParts.PartID

WHERE (((Parts.PartNumber) Like "*"  & [Forms]![frmReturn]![SearchPart] & "*")) OR  (((Parts.PartCode) Like "*" & [Forms]![frmReturn]![SearchPart] &  "*"));

DeliveryDocuments and Parts table are connected via DeliveryDocumentsParts table...

Where is the problem?

[rpeare]

you have some unusual bracketing in your SQL statement, are you cutting and pasting the SQL statement from your database or retyping it because I don't think it'll work at all.

You're not aliasing the portion of your statement inside the brackets in your FROM line, nor are you selecting fields from that join.
If you alias the part inside the bracket you have to use the correct alias in the SELECT line

You also haven't stated clearly what you're trying to do with this query. What you are trying to do determines your approach.

If you are simply trying to get a list of all parts with a record in your deliverydocumentparts table then you don't need the deliverydocuments table at all you can simply do:

SELECT DeliveryDocumentsParts.PartID, Parts.PartNumber, Parts.Code, Parts.NameFROM DeliveryDocumentsParts INNER JOIN Parts ON DeliveryDocumentsParts.PartID = Parts.PartID
WHERE (((Parts.PartNumber) Like "*"  & [Forms]![frmReturn]![SearchPart] & "*")) OR  (((Parts.PartCode) Like "*" & [Forms]![frmReturn]![SearchPart] &  "*"));

Left joins really only need to be used if the source table is allowed to have a blank value but you still want to show the record or some other similar situation. Because I would expect every record in your deliverydocumentparts table to have a record in your parts table an inner join should be fine.

[faca]

I want to get list of all parts in form and DeliveryDocumentsNumber if exist (from DeliveryDocuments table)..

Parts table:

PartID
PartNumber
PartCode
PartName

DeliveryDocuments table:

DeliveryDocumentID
DeliveryDocumentNumber
Date
Store
Shipping

DeliveryDocumentsParts table:

DeliveryDocumentsPartsID
DeliveryDocumentID
PartID

[rpeare]

doesn't it have to exist in the deliverydocuments table if it exists in the deliverydocumentsparts table? If you've set up your data entry/tables correctly and I understand your table structure, it should be impossible for a record to exist in the deliverydocumentsparts table and not have a deliverydocumentID value.

[faca]

doesn't it have to exist in the deliverydocuments table if it exists in the deliverydocumentsparts table? If you've set up your data entry/tables correctly and I understand your table structure, it should be impossible for a record to exist in the deliverydocumentsparts table and not have a deliverydocumentID value.

Sorry i'm so stupid of course form is empty ... oh god

If i want to list all parts how can i show only last DeliveryDocument number? Beacuse now each part is multiplied with different DeliveryDocumentNumber

For example:

Part is:
ATE BRAKE DISCS

DeliveryDocumentNumbers are:
6534
43SA
54FF
00DD

What i want is -> ATE BRAKE DISCS - 00DD

Now i have like this:

ATE BRAKE DISCS - 6534
ATE BRAKE DISCS - 43SA
ATE BRAKE DISCS - 54FF
ATE BRAKE DISCS - 00DD