代码之家 › 专栏 › 技术社区 › Rominus

为什么此访问报告要编辑基础查询?

ms-access sql

Rominus · 技术社区 · 6 年前

我对访问报告有个奇怪的问题。也就是说,当使用报表时,它会编辑其源查询的代码,并中断查询。基础查询的开头是:

select b.Cont_staff, b.tot_success, c.tot_fail
from
(select Cont_staff, count(Cont_staff) as tot_success
from
    (SELECT Cont_date, Cont_result, Cont_staff
    FROM dbo_UIH_contacts
    where dbo_UIH_contacts.Cont_result = 'Successful')
group by Cont_staff) as b
left join 
(select Cont_staff, count(Cont_staff) as tot_fail
from
    (SELECT Cont_date, Cont_result, Cont_staff
    FROM dbo_UIH_contacts
    where dbo_UIH_contacts.Cont_result = 'Unsuccessful') 
group by Cont_staff) as c
on b.cont_staff = c.cont_staff

报告非常简单,每个字段都有一个字段 Cont_staff , tot_success ,和 tot_fail

查询在第一次创建时正确运行,报表在第一次打开时正确运行。但是,如果关闭报表并重新打开,则查询代码将更改为:

SELECT b.Cont_staff, b.tot_success, c.tot_fail
FROM 
    (SELECT Cont_staff, count(Cont_staff) AS tot_success 
    FROM 
    [SELECT Cont_date, Cont_result, Cont_staff FROM dbo_UIH_contacts where dbo_UIH_contacts].[Cont_result = 'Successful'] 
    AS [%$##@_Alias] 
GROUP BY Cont_staff)  AS b 
LEFT JOIN 
    (SELECT Cont_staff, count(Cont_staff) AS tot_fail 
    FROM 
    [SELECT Cont_date, Cont_result, Cont_staff FROM dbo_UIH_contacts where dbo_UIH_contacts].[Cont_result = 'Unsuccessful'] 
    AS [%$##@_Alias] 
GROUP BY Cont_staff)  AS c 
ON b.cont_staff = c.cont_staff

[%$##@_Alias] .

问题似乎并不在于报表,在设置窗体以使用查询时也会发生同样的情况。

2 回复 | 直到 6 年前

June7 6 年前

尝试对最里面的查询使用别名。

select b.Cont_staff, b.tot_success, c.tot_fail
from (select Cont_staff, count(Cont_staff) as tot_success
      from (SELECT Cont_date, Cont_result, Cont_staff
            FROM dbo_UIH_contacts
            where dbo_UIH_contacts.Cont_result = 'Successful') AS Q1
      group by Cont_staff) as b
left join 
     (select Cont_staff, count(Cont_staff) as tot_fail
      from (SELECT Cont_date, Cont_result, Cont_staff
            FROM dbo_UIH_contacts
            where dbo_UIH_contacts.Cont_result = 'Unsuccessful') AS Q2 
      group by Cont_staff) as c
on b.cont_staff = c.cont_staff;

select b.Cont_staff, b.tot_success, c.tot_fail
from (select Cont_staff, count(*) as tot_success
      FROM dbo_UIH_contacts
      where dbo_UIH_contacts.Cont_result = 'Successful'
      group by Cont_staff) as b
left join 
     (select Cont_staff, count(*) as tot_fail FROM dbo_UIH_contacts
      where dbo_UIH_contacts.Cont_result = 'Unsuccessful')
      group by Cont_staff) as c
on b.cont_staff = c.cont_staff;

或者交叉表:

TRANSFORM Count(ContDate) AS CountOfContDate
SELECT Cont_Staff
FROM dbo_UIH_contacts
GROUP BY Cont_Staff
PIVOT Cont_result IN ("Successful", "Unsuccessful");

Rominus 6 年前

虽然我没有确定代码重写的确切原因,但如果将查询拆分为两个访问查询对象,则不会出现问题。因此,访问似乎无法处理这种三级查询。

qry_Conts_by_dt :

SELECT 
    dbo_UIH_contacts.Cont_key
    , dbo_UIH_contacts.Cont_date
    , dbo_UIH_contacts.Cont_result
    , dbo_UIH_contacts.Cont_staff
FROM dbo_UIH_contacts 
WHERE 
    (((dbo_UIH_contacts.Cont_date)>=[Forms]![frm_Cont_status_by_coord]![bx_start_dt] 
    And (dbo_UIH_contacts.Cont_date)<=[Forms]![frm_Cont_status_by_coord]![bx_end_dt])
;

然后引用第一个查询:

SELECT b.Cont_staff, b.tot_success, c.tot_fail
FROM 
    (SELECT Cont_staff, count(Cont_staff) as tot_success
    FROM  qry_Conts_by_dt
    WHERE qry_Conts_by_dt.Cont_result = 'Successful'
    GROUP BY Cont_staff)  AS b 
LEFT JOIN 
    (SELECT Cont_staff, count(Cont_staff) as tot_fail
    FROM  qry_Conts_by_dt
    WHERE qry_Conts_by_dt.Cont_result = 'Unsuccessful' 
    GROUP BY Cont_staff)  AS c ON b.cont_staff = c.cont_staff;

并将第二个查询用作报表(或与此相关的窗体)的记录源。