CODEWITHSUNDEEP
sqlms-accessunionunion-all
Walshie1987
Walshie1987

Reputation: 443

SQL Union Query - two access tables

I have two identical queries in MS Access, qryLabour and qryMaterials. I need to get a total from three fields: Labour Mins, Laser Mins and $ Cost.

I know this can be done using a union query, but It's been a long time since I used one and I keep getting "Error in FROM Statement".

My SQL is:

SELECT sum(TotalLabour) as TotalLabour,sum(TotalCost) as TotalCost,sum(TotalLaser) as TotalLaser
FROM

(
SELECT Sum([qryLabour].[Labour Mins]) AS TotalLabour, Sum([qryLabour].[$ Cost]) AS TotalCost, Sum([qryLabour].[Laser Mins]) AS TotalLaser
FROM qryLabour;

union all

SELECT Sum([qryMaterial].[Labour Mins]) AS TotalLabour, Sum([qryMaterial].[$ Cost]) AS TotalCost, Sum([qryMaterial].[Laser Mins]) AS TotalLaser
FROM qryMaterial;

)

Please can someone advise where I'm going wrong? If I remove the top level "Select" statement I get two lines with the totals in them, it's when I add the top level statement to consolidate the two.

Upvotes: 0

Views: 63

Answers (2)

Walshie1987
Walshie1987

Reputation: 443

Thanks Jaydip.

I actually just figured it out, I had ; at the end of the internal SELECT statements, I removed this, and made the names in the parent SELECT unique. Working great now.

So the correct code is:

SELECT sum( tempTotalLabour) as TotalLabour,sum( tempTotalCost) as TotalCost,sum( tempTotalLaser) as TotalLaser
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([qryMaterial].[Labour Mins]) AS  tempTotalLabour, Sum([qryMaterial].[$ Cost]) AS  tempTotalCost, Sum([qryMaterial].[Laser Mins]) AS  tempTotalLaser
FROM qryMaterial

) ;

Cheers Chris

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

Note sure, but I think you need an alias: 

SELECT sum(u.TotalLabour) as TotalLabour,sum(u.TotalCost) as TotalCost,sum(u.TotalLaser) as TotalLaser
FROM

(
SELECT Sum([qryLabour].[Labour Mins]) AS TotalLabour, Sum([qryLabour].[$ Cost]) AS TotalCost, Sum([qryLabour].[Laser Mins]) AS TotalLaser
FROM qryLabour

union all

SELECT Sum([qryMaterial].[Labour Mins]) AS TotalLabour, Sum([qryMaterial].[$ Cost]) AS TotalCost, Sum([qryMaterial].[Laser Mins]) AS TotalLaser
FROM qryMaterial

) as u

edit: also remove the ";" inside the subquery, as @jarlh suggested

Upvotes: 2

Related Questions