MSAccess SQL 查询 - 全部与多个表合并 - 重复字段

Question

我试图从三个查询中获取总数 qryBOM 、 qryLabour 和 qryLaser 并将它们列在一行中。

我创建了一个qryTotals过滤器，这是我的代码：

SELECT
  Sum(tempTotalLabour) AS TotalLabour,
  Sum(tempTotalCost) AS TotalCost,
  Sum(tempTotalLaser) AS TotalLaser,
  [TotalLabour] + [TotalCost] + [TotalLaser] AS ProductCost
FROM
      (
    SELECT
      Sum([qryLabour].[Labour Mins]) AS tempTotalLabour,
      Sum([qryLabour].[$ Cost]) AS tempTotalCost,
      Sum([qryLabour].[Laser Mins]) AS tempTotalLabour
    FROM
      qryLabour
    union all
    SELECT
      Sum([qryBOM].[Labour Mins]) AS tempTotalLabour,
      Sum([qryBOM].[$ Cost]) AS tempTotalCost,
      Sum([qryBOM].[Laser Mins]) AS tempTotaMaterial
    FROM
      qryBOM
    union all
    SELECT
      Sum([qryLaser].[Labour Mins]) AS tempTotalLabour,
      Sum([qryLaser].[$ Cost]) AS tempTotalCost,
      Sum([qryLaser].[Laser Mins]) AS tempTotalLaser
    FROM
      qryLaser
  ) AS TotalTable;

但是我Duplicate Output Alias - 'tempTotalLabour'收到错误.请有人帮助我解决我出错的地方，以便我可以解决这个问题并为将来学习。

干杯克里斯

Answer 1

你用了两次tempTotalLabour我做了一次尝试，如下所示

SELECT
  Sum(tempTotalLabour) AS TotalLabour,
  Sum(tempTotalCost) AS TotalCost,
  Sum(tempTotalLaser) AS TotalLaser,
 Sum(tempTotalLabour) + Sum(tempTotalCost) + Sum(tempTotalLaser) AS ProductCost
FROM
      (
    SELECT
      Sum([qryLabour].[Labour Mins]) AS tempTotalLabour,
      Sum([qryLabour].[$ Cost]) AS tempTotalCost,
      Sum([qryLabour].[Laser Mins]) as tempTotalLaser
    FROM
      qryLabour
    union all
    SELECT
      Sum([qryBOM].[Labour Mins]) AS tempTotalLabour,
      Sum([qryBOM].[$ Cost]) AS tempTotalCost,
      Sum([qryBOM].[Laser Mins]) AS tempTotalLaser
    FROM
      qryBOM
    union all
    SELECT
      Sum([qryLaser].[Labour Mins]) AS tempTotalLabour,
      Sum([qryLaser].[$ Cost]) AS tempTotalCost,
      Sum([qryLaser].[Laser Mins]) AS tempTotalLaser
    FROM
      qryLaser
  ) AS TotalTable;