Its working fine when I put INNER JOIN dbo_Stocks.
But I need a LEFT join there and I keep getting the error:
Join expression not supported.
Any clue?
Thank you!
SELECT [dbo_Factures Clients].NoFacture,
dbo_Clients.NoClient,
dbo_Clients.Nom,
dbo_Clients.Adresse,
dbo_Clients.Ville,
dbo_Clients.Province,
dbo_Clients.CodePostal,
dbo_Clients.Email,
dbo_FacturesClientsPDVO.Manufacturier AS Marque,
dbo_FacturesClientsPDVO.Modèle,
dbo_FacturesClientsPDVO.Année,
dbo_FacturesClientsPDVO.NoImmatriculation,
dbo_Articles.NoArticle,
dbo_Articles.Catégorie1,
dbo_Articles.Manufacturier,
dbo_Articles.Description,
dbo_FacturesClientsArticles.Quantité,
dbo_FacturesClientsArticles.PrixUnitaire-(dbo_FacturesClientsArticles.PrixUnitaire*dbo_FacturesClientsArticles.PourcEscompte/100) AS [Prix payé], dbo_Stocks.CoûtMargeBénéficiaire/dbo_Stocks.Quantité AS Coutant, 100-Round(Coutant/[Prix payé]*100,2) AS [% Marge],
[Prix payé]-Coutant AS [Marge nette],
[dbo_Factures Clients].DateMiseAjour AS DateFacturé,
dbo_Représentants.Nom,
[dbo_Factures Clients].NoCommis
FROM ((((dbo_Articles INNER JOIN ((dbo_Clients INNER JOIN [dbo_Factures Clients] ON dbo_Clients.NoClient=[dbo_Factures Clients].NoClient) INNER JOIN dbo_FacturesClientsArticles ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture) ON dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle) LEFT JOIN dbo_FacturesClientsPDVO ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsPDVO.NoFacture) INNER JOIN dbo_Représentants ON dbo_Clients.NoReprésentant=dbo_Représentants.NoReprésentant) LEFT JOIN dbo_Stocks ON ([dbo_Factures Clients].NoFacture=dbo_Stocks.NoRéfCRNoFacture) AND (dbo_Articles.NoArticle=dbo_Stocks.NoArticle)) INNER JOIN dbo_ArticlesPrix ON dbo_Articles.NoArticle=dbo_ArticlesPrix.NoArticle
ORDER BY dbo_Clients.Nom;
INNER JOIN dbo_FacturesClientsArticles ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture) ON dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle)
I think it should be
INNER JOIN dbo_FacturesClientsArticles ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture) AND dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle)
FROM ((((dbo_Articles INNER JOIN ((dbo_Clients INNER JOIN [dbo_Factures Clients] ON dbo_Clients.NoClient=[dbo_Factures Clients].NoClient) INNER JOIN dbo_FacturesClientsArticles ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture) ON dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle) LEFT JOIN dbo_FacturesClientsPDVO ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsPDVO.NoFacture) INNER JOIN dbo_Représentants ON dbo_Clients.NoReprésentant=dbo_Représentants.NoReprésentant) LEFT JOIN dbo_Stocks ON ([dbo_Factures Clients].NoFacture=dbo_Stocks.NoRéfCRNoFacture) AND (dbo_Articles.NoArticle=dbo_Stocks.NoArticle)) INNER JOIN dbo_ArticlesPrix ON dbo_Articles.NoArticle=dbo_ArticlesPrix.NoArticle
so i reformatted it like this –
FROM (
(
(
(
dbo_Articles
INNER
JOIN (
(
dbo_Clients
INNER
JOIN [dbo_Factures Clients]
ON dbo_Clients.NoClient=[dbo_Factures Clients].NoClient
)
INNER
JOIN dbo_FacturesClientsArticles
ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture
)
ON dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle
)
LEFT
JOIN dbo_FacturesClientsPDVO
ON [dbo_Factures Clients].NoFacture=dbo_FacturesClientsPDVO.NoFacture
)
INNER
JOIN dbo_Représentants
ON dbo_Clients.NoReprésentant=dbo_Représentants.NoReprésentant
)
LEFT
JOIN dbo_Stocks
ON (
[dbo_Factures Clients].NoFacture=dbo_Stocks.NoRéfCRNoFacture
)
AND (
dbo_Articles.NoArticle=dbo_Stocks.NoArticle
)
)
INNER
JOIN dbo_ArticlesPrix
ON dbo_Articles.NoArticle=dbo_ArticlesPrix.NoArticle
my goodness what strange parenthesizing and nesting of joins you have, grandma
those two ON clauses in a row really threw me off
so i tried to fix everything like this, let me know if it works –
FROM ((((((
dbo_Clients
INNER
JOIN dbo_Représentants
ON dbo_Représentants.NoReprésentant = dbo_Clients.NoReprésentant
)
INNER
JOIN [dbo_Factures Clients]
ON [dbo_Factures Clients].NoClient = dbo_Clients.NoClient
)
INNER
JOIN dbo_FacturesClientsArticles
ON dbo_FacturesClientsArticles.NoFacture = [dbo_Factures Clients].NoFacture
)
INNER
JOIN dbo_Articles
ON dbo_Articles.NoArticle = dbo_FacturesClientsArticles.NoArticle
)
INNER
JOIN dbo_ArticlesPrix
ON dbo_ArticlesPrix.NoArticle = dbo_Articles.NoArticle
)
LEFT
JOIN dbo_FacturesClientsPDVO
ON dbo_FacturesClientsPDVO.NoFacture = [dbo_Factures Clients].NoFacture
)
LEFT
JOIN dbo_Stocks
ON ( dbo_Stocks.NoRéfCRNoFacture = [dbo_Factures Clients].NoFacture
AND dbo_Stocks.NoArticle = dbo_Articles.NoArticle
)
I meant to say change the AND to ON since the first LEFT JOIN seemed to allow for it (though it made no sense to me how it worked)
But it looks like the answer is simpler, and Rudy has already posted it in an earlier thread. Wrap the two conditions in parenthesis…
So, the condition would be
INNER JOIN dbo_FacturesClientsArticles ON ([dbo_Factures Clients].NoFacture=dbo_FacturesClientsArticles.NoFacture AND dbo_Articles.NoArticle=dbo_FacturesClientsArticles.NoArticle)
