Grand Total Problem

[Guest]

Hi,

I have a query that pulls in the Grand Total Score from a number of other
queries depending on their stage (eg. Documentation, Financial etc). In the
query I work out the percentages etc. It works fine with one Grand Total
Score Query (currently the Documentation one) but when I put another query in
(the Financial one) this error message appears.

'The specified field [Grand total Score ] could refer to one or more table
listed in the FROM clause of the SQL statement.'

I know this is because I have two Grand Total Scores from the Documentation
and Financial queries, and that I am not defining which query they come from
properly.

But i have no idea how to fix it!

I've posted the SQL statement below.

Any help would be great..

Phil


SELECT [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand Total
Score] AS [Total (Documentation)], [Template Version Table].[TMS
(Documentation)], [Grand Total Score]/[TMS (Documentation)]*100 AS
[Documentation Percentage], [qry: Total Actual Score (Financial)].[Grand
Total Score]
FROM (([KPI Project Control] INNER JOIN [qry: Total Actual Score
(Documentation)] ON [KPI Project Control].[Project Number] = [qry: Total
Actual Score (Documentation)].[Project Number]) INNER JOIN [Template Version
Table] ON ([KPI Project Control].[Project Number] = [Template Version
Table].[Project Number]) AND ([qry: Total Actual Score
(Documentation)].[Project Number] = [Template Version Table].[Project
Number])) INNER JOIN [qry: Total Actual Score (Financial)] ON [KPI Project
Control].[Project Number] = [qry: Total Actual Score (Financial)].[Project
Number]
GROUP BY [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand Total
Score], [Template Version Table].[TMS (Documentation)], [Grand Total
Score]/[TMS (Documentation)]*100, [qry: Total Actual Score
(Financial)].[Grand Total Score]
HAVING ((([KPI Project Control].Contractor)="Killby & Gayford"));

 

[Guest]

in this part > [Grand Total Score]/[TMS (Documentation)]*100 AS

[Documentation Percentage], < Grand Total Score does not reference to a specific table as the others do.

- Raoul

Hi,

I have a query that pulls in the Grand Total Score from a number of other
queries depending on their stage (eg. Documentation, Financial etc). In the
query I work out the percentages etc. It works fine with one Grand Total
Score Query (currently the Documentation one) but when I put another query in
(the Financial one) this error message appears.

'The specified field [Grand total Score ] could refer to one or more table
listed in the FROM clause of the SQL statement.'

I know this is because I have two Grand Total Scores from the Documentation
and Financial queries, and that I am not defining which query they come from
properly.

But i have no idea how to fix it!

I've posted the SQL statement below.

Any help would be great..

Phil


SELECT [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand Total
Score] AS [Total (Documentation)], [Template Version Table].[TMS
(Documentation)], [Grand Total Score]/[TMS (Documentation)]*100 AS
[Documentation Percentage], [qry: Total Actual Score (Financial)].[Grand
Total Score]
FROM (([KPI Project Control] INNER JOIN [qry: Total Actual Score
(Documentation)] ON [KPI Project Control].[Project Number] = [qry: Total
Actual Score (Documentation)].[Project Number]) INNER JOIN [Template Version
Table] ON ([KPI Project Control].[Project Number] = [Template Version
Table].[Project Number]) AND ([qry: Total Actual Score
(Documentation)].[Project Number] = [Template Version Table].[Project
Number])) INNER JOIN [qry: Total Actual Score (Financial)] ON [KPI Project
Control].[Project Number] = [qry: Total Actual Score (Financial)].[Project
Number]
GROUP BY [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand Total
Score], [Template Version Table].[TMS (Documentation)], [Grand Total
Score]/[TMS (Documentation)]*100, [qry: Total Actual Score
(Financial)].[Grand Total Score]
HAVING ((([KPI Project Control].Contractor)="Killby & Gayford"));

 

[John Vinson]

'The specified field [Grand total Score ] could refer to one or more table
listed in the FROM clause of the SQL statement.'

I know this is because I have two Grand Total Scores from the Documentation
and Financial queries, and that I am not defining which query they come from
properly.

What you need to do is qualify which instance of [Grand Total Score]
you want by prefixing it with the query name in brackets: e.g. change

SELECT [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand
Total
Score] AS [Total (Documentation)], [Template Version Table].[TMS
(Documentation)], [Grand Total Score]/[TMS (Documentation)]*100 AS
[Documentation Percentage], [qry: Total Actual Score
(Financial)].[Grand
Total Score]

to

SELECT [KPI Project Control].[Project Number], [KPI Project
Control].Contractor, [qry: Total Actual Score (Documentation)].[Grand
Total
Score] AS [Total (Documentation)], [Template Version Table].[TMS
(Documentation)], [qry: Total Actual Score (Documentation)].[Grand
Total Score]/[TMS (Documentation)]*100 AS
[Documentation Percentage], [qry: Total Actual Score
(Financial)].[Grand
Total Score]

John W. Vinson[MVP]