ACCESS SQL语句查询报表问题

point_net 2012-10-12 05:59:27

EQUIPEMENT SOLODATA_TAGNUMBER UNDOSOLODATA_TAGNUMBER UNDOSOLODATA_REMARK11 UNDOSOLODATA_STARTDATE11 UNDOSOLODATA_ENDDATE11 TOTAL
Pompes P-15 2 8
Pompes P-15 tata 2 14
Pompes P-15 tata 6 14
Pompes P-16 12 0
Pompes P-16 dert 0 0
Pompes P-16 dert 0 14

这个是直接从ACCESS上截的图，上面属性显示有点乱，但是能对应上哈
问题是：
倒数第二列，怎么求和啊，我在报表中用somme函数，总是显示错误，是不是因为有些行不是数字呢？我用somme(nz(UNDOSOLODATA_ENDDATE11))
NZ也不行，不知道怎么回事，在SQL程序里就行,比如在SQL程序里SUM(nz(UNDOSOLODATA_ENDDATE11))

因为SQL语句已经挺复杂了，希望直接用报表里的函数解决。不过也把SQL语句附上，如果能从SQL语句里改，那就太好了，谢谢各位
SELECT EQUIPEMENT,SOLODATA_TAGNUMBER,SUM(NZ(UNDOSOLODATA_TAGNUMBER1,0)) AS UNDOSOLODATA_TAGNUMBER,UNDOSOLODATA_REMARK11,UNDOSOLODATA_STARTDATE11,UNDOSOLODATA_ENDDATE11, TOTAL
FROM
(
SELECT COMPONENT_GROUPIDS.DESCRIPTION AS EQUIPEMENT, SOLODATA.TAGNUMBER AS SOLODATA_TAGNUMBER, Count(SOLODATA_TAGNUMBER) AS UNDOSOLODATA_TAGNUMBER1,'' AS UNDOSOLODATA_REMARK11,'' AS UNDOSOLODATA_STARTDATE11,SUM(DATEDIFF("D", NZ(SOLODATA.STARTDATE11,0), NZ(SOLODATA.ENDDATE11,0))) AS UNDOSOLODATA_ENDDATE11, '' AS TOTAL
FROM
COMPONENT_GROUPIDS INNER JOIN SOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = SOLODATA.[GROUPID] WHERE SOLODATA.TAGNUMBER<>" " AND SOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]

AND IIF(isNULL([Forms]![Maintenance_panne].[tag_panne]),1,SOLODATA.TAGNUMBER=[Forms]![Maintenance_panne].[tag_panne]) AND IIF(isNULL([Forms]![Maintenance_panne].[STARTDATE]),1,DATEDIFF("D", NZ(SOLODATA.STARTDATE11,0),[Forms]![Maintenance_panne].[STARTDATE])<=0)
AND IIF(isNULL([Forms]![Maintenance_panne].[ENDDATE]),1,DATEDIFF("D", [Forms]![Maintenance_panne].[ENDDATE], NZ(SOLODATA.ENDDATE11,0))<=0)

GROUP BY COMPONENT_GROUPIDS.DESCRIPTION,SOLODATA.TAGNUMBER
UNION ALL
SELECT COMPONENT_GROUPIDS.DESCRIPTION AS EQUIPEMENT, UNDOSOLODATA.TAGNUMBER AS SOLODATA_TAGNUMBER,Count(SOLODATA_TAGNUMBER) AS UNDOSOLODATA_TAGNUMBER1,'' AS UNDOSOLODATA_REMARK11,'' AS UNDOSOLODATA_STARTDATE11,SUM(DATEDIFF("D", UNDOSOLODATA.STARTDATE11, UNDOSOLODATA.ENDDATE11)) AS UNDOSOLODATA_ENDDATE11, '' AS TOTAL
FROM
COMPONENT_GROUPIDS INNER JOIN UNDOSOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = UNDOSOLODATA.[GROUPID] WHERE UNDOSOLODATA.TAGNUMBER<>" " AND UNDOSOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]

AND IIF(isNULL([Forms]![Maintenance_panne].[tag_panne]),1,UNDOSOLODATA.TAGNUMBER=[Forms]![Maintenance_panne].[tag_panne]) AND IIF(isNULL([Forms]![Maintenance_panne].[STARTDATE]),1,DATEDIFF("D", UNDOSOLODATA.STARTDATE11,[Forms]![Maintenance_panne].[STARTDATE])<=0)
AND IIF(isNULL([Forms]![Maintenance_panne].[ENDDATE]),1,DATEDIFF("D", [Forms]![Maintenance_panne].[ENDDATE], UNDOSOLODATA.ENDDATE11)<=0)

GROUP BY COMPONENT_GROUPIDS.DESCRIPTION,UNDOSOLODATA.TAGNUMBER

UNION ALL

SELECT COMPONENT_GROUPIDS.DESCRIPTION AS EQUIPEMENT, SOLODATA.TAGNUMBER AS SOLODATA_TAGNUMBER,'' AS UNDOSOLODATA_TAGNUMBER1, NZ(SOLODATA.REMARK11,'') AS UNDOSOLODATA_REMARK11,NZ(DATEDIFF("D", NZ(SOLODATA.STARTDATE11,0), NZ(SOLODATA.ENDDATE11,0)),0) AS UNDOSOLODATA_STARTDATE11,
'' AS UNDOSOLODATA_ENDDATE11,
(
SELECT SUM(DATEDIFF("D", NZ(SOLODATA.STARTDATE11,0), NZ(SOLODATA.ENDDATE11,0)))
FROM COMPONENT_GROUPIDS INNER JOIN SOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = SOLODATA.[GROUPID] WHERE SOLODATA.TAGNUMBER<>" " AND SOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]
) AS TOTAL
FROM
COMPONENT_GROUPIDS INNER JOIN SOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = SOLODATA.[GROUPID] WHERE SOLODATA.TAGNUMBER<>" " AND SOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]

AND IIF(isNULL([Forms]![Maintenance_panne].[tag_panne]),1,SOLODATA.TAGNUMBER=[Forms]![Maintenance_panne].[tag_panne]) AND IIF(isNULL([Forms]![Maintenance_panne].[STARTDATE]),1,DATEDIFF("D", NZ(SOLODATA.STARTDATE11,0),[Forms]![Maintenance_panne].[STARTDATE])<=0)
AND IIF(isNULL([Forms]![Maintenance_panne].[ENDDATE]),1,DATEDIFF("D", [Forms]![Maintenance_panne].[ENDDATE], NZ(SOLODATA.ENDDATE11,0))<=0)

UNION ALL
SELECT COMPONENT_GROUPIDS.DESCRIPTION AS EQUIPEMENT, UNDOSOLODATA.TAGNUMBER AS SOLODATA_TAGNUMBER,'' AS UNDOSOLODATA_TAGNUMBER1, NZ(UNDOSOLODATA.REMARK11,'') AS UNDOSOLODATA_REMARK11,NZ(DATEDIFF("D", UNDOSOLODATA.STARTDATE11, UNDOSOLODATA.ENDDATE11),0) AS UNDOSOLODATA_STARTDATE11,
'' AS UNDOSOLODATA_ENDDATE11,
(
SELECT SUM(DATEDIFF("D", UNDOSOLODATA.STARTDATE11, UNDOSOLODATA.ENDDATE11))
FROM COMPONENT_GROUPIDS INNER JOIN UNDOSOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = UNDOSOLODATA.[GROUPID] WHERE UNDOSOLODATA.TAGNUMBER<>" " AND UNDOSOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]
) AS TOTAL
FROM
COMPONENT_GROUPIDS INNER JOIN UNDOSOLODATA ON COMPONENT_GROUPIDS.[GROUPID] = UNDOSOLODATA.[GROUPID] WHERE UNDOSOLODATA.TAGNUMBER<>" " AND UNDOSOLODATA.EXTN24=0 AND COMPONENT_GROUPIDS.DESCRIPTION=[Forms]![Maintenance_panne].[DESCRIPTION]

AND IIF(isNULL([Forms]![Maintenance_panne].[tag_panne]),1,UNDOSOLODATA.TAGNUMBER=[Forms]![Maintenance_panne].[tag_panne]) AND IIF(isNULL([Forms]![Maintenance_panne].[STARTDATE]),1,DATEDIFF("D", UNDOSOLODATA.STARTDATE11,[Forms]![Maintenance_panne].[STARTDATE])<=0)
AND IIF(isNULL([Forms]![Maintenance_panne].[ENDDATE]),1,DATEDIFF("D", [Forms]![Maintenance_panne].[ENDDATE], UNDOSOLODATA.ENDDATE11)<=0)

ORDER BY SOLODATA_TAGNUMBER, UNDOSOLODATA_TAGNUMBER1 DESC
)
GROUP BY EQUIPEMENT,SOLODATA_TAGNUMBER,UNDOSOLODATA_REMARK11,UNDOSOLODATA_STARTDATE11,UNDOSOLODATA_ENDDATE11,TOTAL

...全文