将TSQL转换为MS-Access SQL

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 ... "

TSQL(在MS SQL Server 2000和2005中使用)允许多个JOIN子句,一个接一个,不需要逗号或括号。在Access中尝试这个并且它会抛出一个拟合:“查询表达式中的语法错误(缺少运算符)......”

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.

从我在Google-land中收集的内容来看,Access SQL希望使用括号对JOIN子句进行分组。关于如何实现这一点的大多数建议是使用设计视图或查询向导,并让Access确定放置括号的位置(标准SQL中不需要)。问题是,我习惯于在文本编辑器(记事本,SSMS,VS2005,无论如何)中执行我的SQL,设计视图和向导会妨碍我的皮肤爬行。有时,如果有多种可能性,向导会对加入的内容做出错误的假设,并且我已经习惯于在TSQL中自己做这件事,我宁愿让奇才离开它。

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?

是不是有一个工具可以将TSQL转换为Access SQL,或者至少有一套规则来放置括号?

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

2 个解决方案

#1

Yah, MS-Access is dumb.

是的,MS-Access是愚蠢的。

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.

我认为不存在(从MS-SQL / TSQL到MS-Access可能不是一个巨大的市场)。通常,我使用的设计视图就我而言并不是真正的向导。然后我手动添加表格,然后(如果我没有创建正确的关系图表,或者某些东西有点时髦)在Designer中手动创建关系。之后,我在SQL视图中检查查询并根据需要进行更正。

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)

(如果你有N个内连接,你将在开头需要N-1个左括号,并且在连接的最后一个需要一个;不包括最后一个)

#2

This works in Access.

这适用于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.

如您所见,要连接的表组合在一起。

#1