Jet/Access Rearranges SQL?

[Smartin]

Just a curiosity...

Of course we are all used to hand-typed queries being rearranged once
saved. Parenthesis are added to excess, aliases are sometimes created,
etc.

What I'm talking about is a SQL statement that is rearranged by Jet
(Access?) /every/ time it is saved. Here's an example:

I saved the following SQL:
SELECT C.ID INTO PYR_EXCLUDE
FROM CAT_PYR AS C INNER JOIN EXCLUSION_RULES AS E ON
((C.DESK_COD LIKE E.DESK_COD & "*") AND
(C.CLM_NUM LIKE E.CLM_NUM & "*") AND
(C.ACC_NUM LIKE E.ACC_NUM & "*") AND
(C.MED_ONLY LIKE E.MED_ONLY & "*"));

Reopen the query design and you have:
SELECT C.ID INTO PYR_EXCLUDE
FROM CAT_PYR AS C INNER JOIN EXCLUSION_RULES AS E ON (C.DESK_COD LIKE
E.DESK_COD & "*") AND
(C.CLM_NUM LIKE E.CLM_NUM & "*") AND
(C.ACC_NUM LIKE E.ACC_NUM & "*") AND
(C.MED_ONLY LIKE E.MED_ONLY & "*");

Of course, I expect such changes after the first save. But, save this
again and reopen the query design and you have:
SELECT C.ID INTO PYR_EXCLUDE
FROM CAT_PYR AS C INNER JOIN EXCLUSION_RULES AS E ON (C.MED_ONLY LIKE
E.MED_ONLY & "*") AND (C.ACC_NUM LIKE E.ACC_NUM & "*") AND (C.CLM_NUM
LIKE E.CLM_NUM & "*") AND (C.DESK_COD LIKE E.DESK_COD & "*");

Save and reopen yet again and you have:
SELECT C.ID INTO PYR_EXCLUDE
FROM CAT_PYR AS C INNER JOIN EXCLUSION_RULES AS E ON (C.DESK_COD LIKE
E.DESK_COD & "*") AND (C.CLM_NUM LIKE E.CLM_NUM & "*") AND (C.ACC_NUM
LIKE E.ACC_NUM & "*") AND (C.MED_ONLY LIKE E.MED_ONLY & "*");

Continue this ad infinitum and the SQL will alternate between the last
two versions above. Strange!

I've never seen this before. FWIW this is the first time I've used so
many unconventional join operations.

 

[Allen Browne]

The query designer does re-write the SQL any time you make a change in
design view.

You can avoid this to a limited extent by saving the query in SQL View. Then
open it into SQL View, and it should save the phrases in the same order and
leave out the spurious brackets and aliases. However, there are some things
(such as your line-endings) that still get messed up.

One alternative is to save the query statements exactly as you want them in
the Memo field in a table. This at least allows you to edit them the way you
want and paste them into SQL View of the query.

While that's probably not a satisifying reply, hopefully it's of use use.