Greetings,
I have the following query working well via the SQL Server Management Studio
It is putting up a fight porting it to Access.
I added one LEFT JOIN, works...
I add an additional LEFT JOIN, BANG!!!
Suggestions please?
I have the following query working well via the SQL Server Management Studio
Code:
SELECT [p].[id], [p].[authid], [p].[prodteamid], [pt].[teamname], [p].[subsystypeid], [pss].[title], [p].[buyercodetypeid], [pbc].[title], [p].[stocktypeid], [ps].[title], [p].[mfgmethtypeid], [pmm].[title], [p].[cmdtycodetypeid], [pcc].[title], [p].[quoteid], [p].[partnumber], [p].[title], [p].[rev], [p].[ver], [p].[econumber], [p].[qtyper], [p].[eau], [p].[stopworkflg], [p].[stdcost], [p].[supplierchampion], [p].[comments]
FROM [dbo].[parts] AS [p]
LEFT JOIN [dbo].[partsbuyercodetype] AS [pbc] ON [p].[buyercodetypeid] = [pbc].[id]
LEFT JOIN [dbo].[partscmdtycodetype] AS [pcc] ON [p].[cmdtycodetypeid] = [pcc].[id]
LEFT JOIN [dbo].[partsmfgmethtype] AS [pmm] ON [p].[mfgmethtypeid] = [pmm].[id]
LEFT JOIN [dbo].[partsstocktype] AS [ps] ON [p].[stocktypeid] = [ps].[id]
LEFT JOIN [dbo].[partssubsystype] AS [pss] ON [p].[subsystypeid] = [pss].[id]
LEFT JOIN [dbo].[prodteam] AS [pt] ON [p].[prodteamid] = [pt].[id];
So I paired it WAY back... to just the parts table. This works:"Syntax error (missing operator) in query expression 'p.buyercodetypeid = pbc.id
LEFT JOIN dbo_partscmdtycodetype AS pcc ON p.cmdtycodetypeid = pcc.i'."
Code:
SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, p.stocktypeid, p.mfgmethtypeid, p.cmdtycodetypeid, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p;
Code:
SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, pbc.title AS pbctitle, p.stocktypeid, p.mfgmethtypeid, p.cmdtycodetypeid, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p
LEFT JOIN dbo_partsbuyercodetype AS pbc ON p.buyercodetypeid = pbc.id;
Code:
SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, pbc.title AS pbctitle, p.stocktypeid, ps.title, p.mfgmethtypeid, p.cmdtycodetypeid, pcc.title AS pcctitle, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p
LEFT JOIN dbo_partsbuyercodetype AS pbc ON p.buyercodetypeid = pbc.id
LEFT JOIN dbo_partscmdtycodetype AS pcc ON p.cmdtycodetypeid = pcc.id;