Convert TSQL to MS-Access SQL

Convert TSQL to MS-Access SQL

如日中天 发布于 2021-11-26 字数 1847 浏览 945 回复 2 原文

TSQL (as used in MS SQL Server 2000 and 2005) allows multiple JOIN clauses, one right after the other, no commas or parentheses needed. Try this in Access and it throws a fit:
"Syntax error (missing operator) in query expression ... "

From what I have been able to gather out in Google-land, Access SQL wants parentheses to group the JOIN clauses. Most advice on how to accomplish this is to use the design view or the query wizard, and let Access figure out where to put the parentheses (that are NOT required in standard SQL). Problem is, I am so used to doing my SQL in a text editor (Notepad, SSMS, VS2005, whatever) that the design view and the wizard get in the way and make my skin crawl. Sometimes the wizards make bad assumptions about what to join if there are multiple possibilities, and I'm so used to doing it myself in TSQL that I'd rather leave the wizards out of it.

Isn't there a tool that will convert TSQL into Access SQL, or at least a set of rules on where to put the parentheses?

Example:

SELECT ...
FROM Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID
    INNER JOIN Participant-Probation PXP 
        ON PAR.ID = PXP.ParticipantID
    INNER JOIN Probation PBN 
        ON PXP.ProbationID = PBN.ID
    INNER JOIN Class-Participant CXP 
        ON PAR.ID = CXP.ParticipantID
    INNER JOIN Class CLS 
        ON CXP.ClassID = CLS.ID
    INNER JOIN Official OFR 
        ON PAR.ReferringPO = OFR.ID
    INNER JOIN Participant-Official PXO 
        ON PAR.ID = PXO.ParticipantID
    INNER JOIN Official OFA 
        ON PXO.OfficialID = OFA.ID

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

梦魇绽荼蘼 2022-06-07 2 楼

This works in Access.

SELECT *
FROM (((Individual AS IND 

INNER JOIN Ethnicity AS ETH 
    ON IND.EthnicityID = ETH.ID) 

INNER JOIN Education AS EDU 
    ON IND.EducationID = EDU.ID) 

INNER JOIN Marital AS MAR 
    ON IND.Marital = MAR.ID) 

INNER JOIN (((((((Participant AS PAR 

    INNER JOIN Official AS OFR 
        ON PAR.ReferringPO = OFR.ID) 

    INNER JOIN [Class-Participant] AS CXP 
        ON PAR.ID = CXP.ParticipantID) 

    INNER JOIN Class AS CLS 
        ON CXP.ClassID = CLS.ID) 

    INNER JOIN [Participant-Official] AS PXO 
        ON PAR.ID = PXO.ParticipantID) 

    INNER JOIN Official AS OFA 
        ON PXO.OfficialID = OFA.ID) 

    INNER JOIN [Participant-Probation] AS PXP 
        ON PAR.ID = PXP.ParticipantID) 

    INNER JOIN Probation AS PBN 
        ON PXP.ProbationID = PBN.ID) 

 ON IND.APETSID = PAR.APETSID

As you can see, the tables to be joined are grouped together.

冷心人i 2022-06-07 1 楼

Yah, MS-Access is dumb.

I don't think one exists (probably not a huge market either to go from MS-SQL/TSQL to MS-Access). Typically, I use the Design View which is not really a wizard as far as I'm concerned. I then manually add the tables, and then (if I haven't created a proper Relations ship diagram, or something is a little funky) manually create the relationships in the Designer. After that, I check the query in the SQL view and correct as need be.

In the case of your example (as you indicated) you probably need the parenthesis, and will have to manually add them. You probably want something like this:

SELECT ...
FROM (((Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID)
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID)
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID)
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID

(if you have N inner joins, you will need N-1 open-parenthesis at the beginning, and one on ever end of the join; excluding the last one)