无法添加表格( [英] Could not add the table (

问题描述

以下代码在Sage200中有效.

The following code works in Sage200.

SELECT bcs.BomReference
    ,bcs.DateTimeCosted
    ,bcs.TotalCost
FROM (
    SELECT BomReference
        ,Max(DateTimeCosted) AS MaxDate
    FROM NDM_Sage200.dbo.BomCostSession BomCostSession
    GROUP BY BomReference
    ) AS ldc
INNER JOIN BomCostSession AS bcs ON bcs.BomReference = ldc.BomReference
    AND bcs.DateTimeCosted = ldc.MaxDate
ORDER BY BomReference

当我尝试使用INNER JOIN将其扩展到另一个表以获取更多列时(使用 BomReference )，我得到了错误消息:无法添加表格(.
参见下面的修改代码示例；我必须使用2个联接才能到达所需的表，但是无论我联接到工作代码中如何，都遇到相同的错误.

As soon as I try extending this with an INNER JOIN to another table to get more columns (using BomReference), I get the error message: Could not add the table (.
See below for example of modified code; I have to use 2 joins to get to the table I need, but have the same error whatever I join onto the working code.

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost, BomBuildProduct.StockDescription 
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
BomCostSession as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
***** Fails when adding INNER JOIN here *****
    INNER JOIN
    BomBuildPackage
    ON BomCostSession.BomBuildPackageID = BomBuildPackage.BomBuildPackageID
    INNER JOIN
    BomBuildProduct
    ON BomBuildPackage.BomRecordID = BomBuildProduct.BomRecordID
    ORDER BY BomReference

我在做什么错?我需要使用来自多个表的数据来扩展查询.
我还认为，在适用的部分上使用MSQuery时，它不提供添加任何表的选项-这使得尝试选项非常困难.
为什么?

What am I doing wrong ? I need to expand the query with data from several tables.
I also think that when using MSQuery on the section that works, it offers no options to add any tables - this makes it rather difficult to try options.
Why ?

推荐答案

MSQuery的问题在于，它试图在其设计视图中以图形方式显示您的查询，这对于简单查询有效，但对于通常会生成无法添加表格消息.我发现的方法是将查询视为包装查询中的一个大子查询，这迫使MSQuery放弃设计视图并以纯SQL文本形式工作.

The issue with MSQuery is that it attempts to display your query graphically in it's design view, this works OK for simple queries but not for complex queries which usually generates the could not add table message. The way I found around this is to treat your query as one big sub query inside a wrapper query, this forces MSQuery to give up the design view and work as pure SQL text.

另一个问题可能是，对于一个表，您具有完整路径，但对于其他表则没有完整路径，它是否对包含它的表是正确的，是否需要在其他表上使用?

Another issue might be that for one table you have the full path but not the others, is it correct for the table you have included it and does it need to be used on the other tables.

以下是我认为您应该进行的更改的示例:

Here is an example of the changes I think you should make:

SELECT * FROM (
SELECT bcs.BomReference
  ,bcs.DateTimeCosted
  ,bcs.TotalCost
  ,BomBuildProduct.StockDescription
FROM 
  (SELECT BomReference
     ,Max(DateTimeCosted) AS MaxDate
   FROM NDM_Sage200.dbo.BomCostSession BomCostSession
   GROUP BY BomReference) AS ldc
INNER JOIN NDM_Sage200.dbo.BomCostSession AS bcs ON bcs.BomReference = ldc.BomReference
  AND bcs.DateTimeCosted = ldc.MaxDate
INNER JOIN NDM_Sage200.dbo.BomBuildPackage ON BomCostSession.BomBuildPackageID = BomBuildPackage.BomBuildPackageID
INNER JOIN NDM_Sage200.dbo.BomBuildProduct ON BomBuildPackage.BomRecordID = BomBuildProduct.BomRecordID) x
ORDER BY BomReference

这篇关于无法添加表格(的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！