If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grand Total Problem
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")); |
#2
|
|||
|
|||
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 "PW11111" wrote: 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")); |
#3
|
|||
|
|||
On Fri, 11 Mar 2005 02:11:04 -0800, "PW11111"
wrote: '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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IE6 & Outlook Problem (Strange Problem)! | KW | Outlook Express | 4 | February 1st, 2005 08:31 AM |
Reinstalling OE... | KAR | Outlook Express | 24 | August 21st, 2004 06:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Fromatting Problem w/ Document Created in Works | Inky | Formatting Long Documents | 2 | May 4th, 2004 04:22 AM |
Word 2000 footnote problem: footnotes consistently too high (again) | Lori | Formatting Long Documents | 2 | May 1st, 2004 07:15 PM |