CODEWITHSUNDEEP
sqldatabasems-access
Bob P
Bob P

Reputation: 237

I can't get this SQL to work, can anyone spot it?

SELECT 
    [Stock Conversion Items].SCID, 
    [Stock Conversion Items].[Result PC], 
    [Stock Conversion Items].[Quantity], 
    [Stock Conversion].[Date], 
    [products/stock].[Description] 
FROM 
    [Stock Conversion Items] 
INNER JOIN [Stock Conversion] 
    ON [Stock Conversion Items].SCID = [Stock Conversion].SCID 
INNER JOIN [products/stock] 
    ON [Stock Conversion Items].[Result PC] = [products/stock].[Product Code] 
ORDER BY [Stock Conversion Items].SCID;

This code is pretty self explanatory, basically I get a syntax operator error that I can't solve at all.

Upvotes: 0

Views: 75

Answers (3)

Bob P
Bob P

Reputation: 237

        SELECT [Stock Conversion Items].SCID,
     [Stock Conversion Items].[Result PC],
     [Stock Conversion Items].Quantity,
     [products/stock].Description,
     [Stock Conversion].[SC Date],
     [Stock Conversion].[Created By]

     FROM [Stock Conversion Items]
      INNER JOIN ([products/stock] INNER JOIN [Stock Conversion]
        ON [products/stock].[Product Code] = [Stock Conversion].[Source PC])
        ON [Stock Conversion Items].SCID = [Stock Conversion].SCID;

So this is what Access has come up with to solve my problem, managed to get the thing working, think it may of been a data type mismatch. So thanks Sweko.

Upvotes: 0

Tom
Tom

Reputation: 6663

For some reason Access seems to want to put parenthesis around the first join. This seems to work:

SELECT [Stock Conversion Items].SCID, [Stock Conversion Items].[Result PC], [Stock Conversion Items].Quantity, [Stock Conversion].Date, [products/stock].Description
FROM ([Stock Conversion] INNER JOIN [Stock Conversion Items] ON [Stock Conversion].SCID = [Stock Conversion Items].SCID) INNER JOIN [products/stock] ON [Stock Conversion Items].[Result PC] = [products/stock].[Product Code];

Upvotes: 0

SWeko
SWeko

Reputation: 30882

Reformated the query a bit, and got it looking like this:

SELECT sci.SCID, sci.[Result PC], sci.Quantity, sci.Date, s.Description
FROM [Stock Conversion Items] sci
INNER JOIN [Stock Conversion] sc 
   ON sci.SCID = sc.SCID 
INNER JOIN [products/stock] s 
   ON sci.[Result PC] = s.[Product Code] 
ORDER BY sci.SCID;

The only thing that can fail in that statement are the on clauses. Are you sure that both SCID columns are of the same type, as well as the [Result PC] and [Product Code] columns?

Upvotes: 3

Related Questions