Use an alias in a query

[sharonc]

I have created the following query, it uses alias. I have tried placing the alias in single and double quotes and it is still kicking out as an error. How do you use an alias in a query?

SELECT DISTINCT Sum([Construction Cost]) AS [SubTotal Construction], [SubTotal Construction]*[Contractor Tax Rate] AS Tax,
FROM JobConstructionCost INNER JOIN [JobDetails Query] ON (JobConstructionCost.[Project Number] = [JobDetails Query].[Project Number]) AND (JobConstructionCost.[Job Number] = [JobDetails Query].[Job Number])
GROUP BY [JobDetails Query].[Project Number], [JobDetails Query].[Job Number], [JobDetails Query].[Contractor Tax Rate]

 

[tlbroadbent]

Aliases are certainly available. Brackets or double quotes should work. Better yet, remove the spaces and you'll not need either.

You CANNOT use an alias name later in the same query.

I'd also remove the DISTINCT clause as it is redundant.

Here are a couple of ways to rewrite the query.

SELECT
Sum([Construction Cost]) AS [SubTotal Construction],
Sum([Construction Cost]*[Contractor Tax Rate]) AS Tax,

FROM JobConstructionCost As c
INNER JOIN [JobDetails Query] As j
ON c.[Project Number] = j.[Project Number]
AND c.[Job Number] = j.[Job Number]
GROUP BY j.[Project Number], j.[Job Number]

OR

SELECT c.[Project Number], c.[Job Number],
c.[SubTotal Construction],
c.[Construction Cost]*j.[Contractor Tax Rate] AS Tax

(SELECT
[Project Number], [Job Number]
Sum([Construction Cost]) AS [SubTotal Construction]

FROM JobConstructionCost
GROUP BY [Project Number], [Job Number]) As c

INNER JOIN [JobDetails Query] As j
ON c.[Project Number] = j.[Project Number]
AND c.[Job Number] = j.[Job Number] Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[sharonc]

Thank you for answering my question.