MSAccess SQL Query - Union all with multiple tables - Duplicate field

Question

I'm trying to take the total from three queries qryBOM , qryLabour and qryLaser and have them listed on one line.

I've create a qryTotals Filter and here's my code:

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;

But I'm getting the error Duplicate Output Alias - 'tempTotalLabour'. Please can someone help me with where I'm going wrong so I can fix this and learn for future.

Cheers Chris

This can't be the SQL you're running to get that error message. The error refers to `tempLabourOutput` which is no where in your query. Please include the actual query that generates the actual error message. — MatBailie, Mar 26 '19 at 10:33
Sorry you are right, I mistyped. It should have been `tempTotalLabour` — Walshie1987, Mar 26 '19 at 10:36
You must learn to proofread code. There is no issue here apart from obvious typos and misspelling. — Gustav, Mar 26 '19 at 10:36
Apologies, I was using Access Query Editor and it was all on one line, I only formatted in the above way to make it easier to read for Stack Users - which I should have doubled checked as I can now spot the issue — Walshie1987, Mar 26 '19 at 11:02

score 2 · Accepted Answer · answered Mar 26 '19 at 10:21

you used tempTotalLabour two times i made it single try like below

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;

MSAccess SQL Query - Union all with multiple tables - Duplicate field

1 Answers1