CODEWITHSUNDEEP
sqlms-access
Tracy
Tracy

Reputation: 61

MS Access SQL is removing parentheses from query

I have this query which works fine. When I open up create query and copy/paste the SQL in the window, it runs fine, and I can edit the form number and save the query. After I close the query and then open it up again in Design View, the parentheses are all gone, and when I try to change anything or save it I get Join Expression Not Supported. The query runs regardless, and the results are correct. What can I do to keep the query from being changed like this? Why is it removing the parentheses?

SELECT DISTINCT 
A01.ID,
A01.Symbol,
A01.Number,
A01.Module,
A02.Form as FormHE3, 
MAX (A02.Sequence) as FormSeq
INTO [CodesAndFormsResults]
FROM 
(01_PreviousTable A01
left outer join tbl_PolForms A02 ON (A02.ID = A01.ID 
and A02.Form = 'HE3'))
GROUP BY 
A02.Form, 
A01.ID, 
A01.Symbol,
A01.Number, 
A01.Module
ORDER BY
A01.Number,A01.Symbol,A01.Module;

Upvotes: 1

Views: 1531

Answers (2)

HansUp
HansUp

Reputation: 97101

  1. SELECT DISTINCT is redundant in a GROUP BY query; get rid of DISTINCT.
  2. Discard INTO [CodesAndFormsResults] while you focus on building a plain SELECT query which returns what you want and which Access' query designer will not mangle. You can add INTO [CodesAndFormsResults] back later, but for now limit yourself to the simplest query possible.
  3. Move Form = 'HE3' from the join ON clause to a WHERE clause in a subquery based on tbl_PolForms.
SELECT
    A01.ID,
    A01.Symbol,
    A01.Number,
    A01.Module,
    A02.Form AS FormHE3, 
    MAX(A02.Sequence) AS FormSeq
FROM 
    [01_PreviousTable] AS A01
    LEFT JOIN 
    (
        SELECT t.ID, t.Sequence, t.Form
        FROM tbl_PolForms AS t
        WHERE t.Form = 'HE3'
    ) AS A02
    ON A01.ID = A02.ID 
GROUP BY 
    A02.Form, 
    A01.ID, 
    A01.Symbol,
    A01.Number, 
    A01.Module
ORDER BY
    A01.Number,
    A01.Symbol,
    A01.Module;

Upvotes: 2

tgolisch
tgolisch

Reputation: 6734

The parentheses are being removed because they are unncecessary. MS Access always tries to simplify a query before saving it. Sometimes you can trick MS Access into leaving it alone, but it usually makes your query more complicated and fragile.

The query parser is complaining ("Join syntax not supported") because it can't display the query visually. This always happens when you specify a conditional join. There really isn't a problem. You just have to edit the query as SQL insteady of via the designer.

Upvotes: 1

Related Questions