none
Syntax error (missing operator) in query expression RRS feed

  • Question

  • Can someone tell me what is wrong with the syntax in this query?

    I'm getting this error:  "Syntax error (missing operator) in query expression 'Round.[RoundID] = Result.[RoundID] 
    INNER JOIN (SELECT Files.[RoundID], COUNT(Files.[RoundID]) AS FileCount FROM Files GROUP BY Files.[RoundID]) AS derivedTable ON Round.[RoundID] = derivedTable.[RoundID'."

    SELECT Round.RoundID, Round.RoundNumber, Round.Analyte, Round.LabID, Round.ReceivedDate, Round.ShipperCold, Round.DryIceInShipper, Round.Assay, Round.Instrument, Round.KitManufacturer, Round.Calibrators, Round.LowCalibrator, Round.HighCalibrator, Round.UnitOfMeasure, Round.LOD, Round.LabNote, Round.OfficialNote, Round.ImportDate, Result.ResultID, Result.SampleID, Result.Run, Result.ReplicateNumber, Result.MeasuredValue, Result.AssayDate, Result.LessThanLOD, Result.Note, derivedTable.FileCount
    FROM Round INNER JOIN Result ON Round.[RoundID] = Result.[RoundID] 
    INNER JOIN (SELECT Files.[RoundID], COUNT(Files.[RoundID]) AS FileCount FROM Files GROUP BY Files.[RoundID]) AS derivedTable ON Round.[RoundID] = derivedTable.[RoundID];



    Ryan

    Tuesday, August 1, 2017 10:32 PM

Answers

  • Try using parentheses around your first join clause to help the query parser know what you mean:

    SELECT Round.RoundID, Round.RoundNumber, Round.Analyte, Round.LabID, Round.ReceivedDate, Round.ShipperCold, Round.DryIceInShipper, Round.Assay, Round.Instrument, Round.KitManufacturer, Round.Calibrators, Round.LowCalibrator, Round.HighCalibrator, Round.UnitOfMeasure, Round.LOD, Round.LabNote, Round.OfficialNote, Round.ImportDate, Result.ResultID, Result.SampleID, Result.Run, Result.ReplicateNumber, Result.MeasuredValue, Result.AssayDate, Result.LessThanLOD, Result.Note, derivedTable.FileCount
    FROM (Round INNER JOIN Result ON Round.[RoundID] = Result.[RoundID])
    INNER JOIN (SELECT Files.[RoundID], COUNT(Files.[RoundID]) AS FileCount FROM Files GROUP BY Files.[RoundID]) AS derivedTable ON Round.[RoundID] = derivedTable.[RoundID];


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Ryan0827 Wednesday, August 2, 2017 11:05 PM
    Wednesday, August 2, 2017 12:05 AM

All replies

  • Hi Ryan, Maybe you have a missing or misplaced parenthesis for the subquery. Sent from phone...
    Tuesday, August 1, 2017 11:41 PM
  • Thanks for the reply .theDBguy, but I don't think I'm missing a parenthesis.  You can see I have the derived table select statement encapsulated by parenthesis as you can see in the SQL.

    Ryan

    Tuesday, August 1, 2017 11:50 PM
  • What does it look like in design view? Is this on Access or SQL Server?
    Tuesday, August 1, 2017 11:55 PM
  • Try using parentheses around your first join clause to help the query parser know what you mean:

    SELECT Round.RoundID, Round.RoundNumber, Round.Analyte, Round.LabID, Round.ReceivedDate, Round.ShipperCold, Round.DryIceInShipper, Round.Assay, Round.Instrument, Round.KitManufacturer, Round.Calibrators, Round.LowCalibrator, Round.HighCalibrator, Round.UnitOfMeasure, Round.LOD, Round.LabNote, Round.OfficialNote, Round.ImportDate, Result.ResultID, Result.SampleID, Result.Run, Result.ReplicateNumber, Result.MeasuredValue, Result.AssayDate, Result.LessThanLOD, Result.Note, derivedTable.FileCount
    FROM (Round INNER JOIN Result ON Round.[RoundID] = Result.[RoundID])
    INNER JOIN (SELECT Files.[RoundID], COUNT(Files.[RoundID]) AS FileCount FROM Files GROUP BY Files.[RoundID]) AS derivedTable ON Round.[RoundID] = derivedTable.[RoundID];


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Ryan0827 Wednesday, August 2, 2017 11:05 PM
    Wednesday, August 2, 2017 12:05 AM