I have following tables and relations.
SO I have a project, that has article groups, in those groups are articles.
The articles can be ordered for a project.
I want to have a querry that generates a table for a selected project and group that shows all the articles and if it is ordered also shows the orderID.
This querry only shows the articles if they are already ordered (so an order line exist).
Code:
SELECT Project_AGroups.PID, Project_AGroups.AGID, ArticleGroup_Articles.AID, OrderLine.OrderIDFROM Projects
INNER JOIN ((Articles INNER JOIN ((ArticleGroups INNER JOIN ArticleGroup_Articles ON ArticleGroups.ID = ArticleGroup_Articles.AGID)
INNER JOIN Project_AGroups ON ArticleGroups.ID = Project_AGroups.AGID) ON Articles.ID = ArticleGroup_Articles.AID)
INNER JOIN OrderLine ON Articles.ID = OrderLine.ArtID) ON Projects.ID = OrderLine.ProjectID;
I thought switching the last inner join to a left join would solve the problem. But Access doenst agree and tells me it is not supported.
Anyone any ideas?
thx,
Wesley