The following query works in MS SQL Server, but does not work in MS Access. I get the error "Invalid precis and the query is below:
select (select max(answer.answer) from answer where answer.member_id in
( select member_id from team_members where project_id in
( select project_id from project where Business_stream='Upstream' and stage='Appraise' and project_id in
(select project_id from projectextra where subteam<>1 ) ) ) and answer.page_id=page.page_id) as thinl,
(select max(avgscore) from task_project where task_project.project_id not in
(select project_id from projectextra where subteam=1 ) and task_project.project_id in
(select project_id from project where stage='Appraise' and Business_stream = 'Upstream') and task_project.page_id=page.page_id) as bmax,
(select max(answer) from answer where answer.page_id=page.page_id) as datamax,
(select avg(avgscore) from task_project where project_id=1 and task_project.page_id=page.page_id) as projavg,
(select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and task_project.page_id=page.page_id) as companyavg,
(select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and project_id in
(select project_id from project where Business_stream = 'Upstream') and task_project.page_id=page.page_id) as businessavg,
page.* from page,riverorder where page.category_name='Business Boundaries' and stage_name='Appraise' and riverorder.category_name=page.category_name order by riverorder.riverorder,page.order_id
Resullts from SQL Server:
ion for decimal data type. " The results are the following:
thinl bmax datamax projavg companyavg businessavg Page_id Stage_name Category_name Page_name Order_id
----- ---------------------------------------- ------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------- -------------------- --------------------------------------------- --------------------------------------------------------------------------- -----------
4.00 4.000000 4.00 NULL 4.000000 4.000000 1 Appraise Business Boundaries What We Stand For (Business Policies) 1
3.00 3.000000 3.00 NULL 3.000000 3.000000 2 Appraise Business Boundaries Business Strategy & Objectives 2
4.00 4.000000 4.00 NULL 4.000000 4.000000 155 Appraise Business Boundaries Business Priority Setting (including Life-Cycle focus) 3
3.00 3.000000 3.00 NULL 3.000000 3.000000 3 Appraise Business Boundaries Performance Targets 4
3.00 3.000000 3.00 NULL 3.000000 3.000000 4 Appraise Business Boundaries Market/Commercial Contraints 5
NULL NULL NULL NULL NULL NULL 5 Appraise Business Boundaries Geo-political Constraints 6
NULL NULL NULL NULL NULL NULL 156 Appraise Business Boundaries Partner Constraints 7
3.00 3.000000 3.00 NULL 3.000000 3.000000 6 Appraise Business Boundaries Stakeholder Constraints 8
4.00 4.000000 4.00 NULL 4.000000 4.000000 7 Appraise Business Boundaries Technology Constraints 9
4.00 4.000000 4.00 NULL 4.000000 4.000000 9 Appraise Business Boundaries Regulatory Environment 10
select (select max(answer.answer) from answer where answer.member_id in
( select member_id from team_members where project_id in
( select project_id from project where Business_stream='Upstream' and stage='Appraise' and project_id in
(select project_id from projectextra where subteam<>1 ) ) ) and answer.page_id=page.page_id) as thinl,
(select max(avgscore) from task_project where task_project.project_id not in
(select project_id from projectextra where subteam=1 ) and task_project.project_id in
(select project_id from project where stage='Appraise' and Business_stream = 'Upstream') and task_project.page_id=page.page_id) as bmax,
(select max(answer) from answer where answer.page_id=page.page_id) as datamax,
(select avg(avgscore) from task_project where project_id=1 and task_project.page_id=page.page_id) as projavg,
(select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and task_project.page_id=page.page_id) as companyavg,
(select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and project_id in
(select project_id from project where Business_stream = 'Upstream') and task_project.page_id=page.page_id) as businessavg,
page.* from page,riverorder where page.category_name='Business Boundaries' and stage_name='Appraise' and riverorder.category_name=page.category_name order by riverorder.riverorder,page.order_id
Resullts from SQL Server:
ion for decimal data type. " The results are the following:
thinl bmax datamax projavg companyavg businessavg Page_id Stage_name Category_name Page_name Order_id
----- ---------------------------------------- ------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------- -------------------- --------------------------------------------- --------------------------------------------------------------------------- -----------
4.00 4.000000 4.00 NULL 4.000000 4.000000 1 Appraise Business Boundaries What We Stand For (Business Policies) 1
3.00 3.000000 3.00 NULL 3.000000 3.000000 2 Appraise Business Boundaries Business Strategy & Objectives 2
4.00 4.000000 4.00 NULL 4.000000 4.000000 155 Appraise Business Boundaries Business Priority Setting (including Life-Cycle focus) 3
3.00 3.000000 3.00 NULL 3.000000 3.000000 3 Appraise Business Boundaries Performance Targets 4
3.00 3.000000 3.00 NULL 3.000000 3.000000 4 Appraise Business Boundaries Market/Commercial Contraints 5
NULL NULL NULL NULL NULL NULL 5 Appraise Business Boundaries Geo-political Constraints 6
NULL NULL NULL NULL NULL NULL 156 Appraise Business Boundaries Partner Constraints 7
3.00 3.000000 3.00 NULL 3.000000 3.000000 6 Appraise Business Boundaries Stakeholder Constraints 8
4.00 4.000000 4.00 NULL 4.000000 4.000000 7 Appraise Business Boundaries Technology Constraints 9
4.00 4.000000 4.00 NULL 4.000000 4.000000 9 Appraise Business Boundaries Regulatory Environment 10