Query Help

[Liliane]

Hi,

I'm writing a query to list different kinds of audit findings against two
different categories: Programs and Audit Groups. The result should be like
this:

Audit_Group Program_ID External_Audits Internal_Audits No_of_Items
Completed Pending Overdue
Strategic 2 1 0 6 6 0 0
Strategic 10 0 8 22 20 2 0
Performance 4 16 8 120 116 4 0
Performance 10 2 1 22 18 4 0
Compliance 1 1 0 2 2 0 0
Compliance 07 2 0 8 8 0 0
Compliance 10 2 29 212 199 13 0
01 226 150 2253 2181 72 0
02 121 89 1377 1305 67 5


But my query brings lots of duplicated results for the "External_Audit" and
"Internal_Audits" columns. I know the two categories (Program and
Audit_Groups) affect to each other, and if I remove audit_groups everything
is all right.

My query is:

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]) AS Auditee,
Sum(IIf([tbl_Audits]![Audit_Type]="EXT",1,0)) AS External_Audits,
Sum(IIf([tbl_Audits]![Audit_Type]="INT",1,0)) AS Internal_Audits,
Sum(IIf([Report_Received]<=[Forms]![frmReports]![ToDate] And
[Carried_out]=True,1,0)) AS No_of_Items, Sum(IIf(Not IsNull([Date_Actioned])
And [Report_Received]<=[Forms]![frmReports]![ToDate] And
[Carried_Out]=True,1,0)) AS Completed, Sum(IIf(IsNull([Date_Actioned]) And
([Status_Date]<=[When] Or IsNull([When])) And
[Report_Received]<=[Forms]![frmReports]![ToDate] And [Carried_Out]=True,1,0))
AS Pending, Sum(IIf(IsNull([Date_Actioned]) And ([When]<[Status_Date]) And
[Report_Received]<=[Forms]![frmReports]![ToDate],1,0)) AS Overdue
FROM ((tbl_Programs INNER JOIN tbl_Projects ON tbl_Programs.Program_ID =
tbl_Projects.Program_ID) INNER JOIN tbl_Work_Packages ON
tbl_Projects.Project_ID = tbl_Work_Packages.Project_ID) INNER JOIN
(tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No) ON tbl_Work_Packages.Work_Package_ID =
tbl_Audits.Work_Package_ID
GROUP BY tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]), tbl_Audits.Carried_Out,
tbl_Programs.Excluded
HAVING (((tbl_Audits.Carried_Out)=True) AND ((tbl_Programs.Excluded)=No))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;


I tried to write 2 queries for the External/Internal columns and the rest
columns, the results are OK. But I can't make them together. Dupulicated
results always come out. The 2 seperate queries are:

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]) AS Auditee,
Sum(IIf([Report_Received]<=[Forms]![frmReports]![ToDate] And
[Carried_out]=True,1,0)) AS No_of_Items, Sum(IIf(Not IsNull([Date_Actioned])
And [Report_Received]<=[Forms]![frmReports]![ToDate] And
[Carried_Out]=True,1,0)) AS Completed, Sum(IIf(IsNull([Date_Actioned]) And
([Status_Date]<=[When] Or IsNull([When])) And
[Report_Received]<=[Forms]![frmReports]![ToDate] And [Carried_Out]=True,1,0))
AS Pending, Sum(IIf(IsNull([Date_Actioned]) And ([When]<[Status_Date]) And
[Report_Received]<=[Forms]![frmReports]![ToDate],1,0)) AS Overdue
FROM ((tbl_Programs INNER JOIN tbl_Projects ON tbl_Programs.Program_ID =
tbl_Projects.Program_ID) INNER JOIN tbl_Work_Packages ON
tbl_Projects.Project_ID = tbl_Work_Packages.Project_ID) INNER JOIN
(tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No) ON tbl_Work_Packages.Work_Package_ID =
tbl_Audits.Work_Package_ID
GROUP BY tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]), tbl_Audits.Carried_Out,
tbl_Programs.Excluded
HAVING (((tbl_Audits.Carried_Out)=True) AND ((tbl_Programs.Excluded)=No))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;

and

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]) AS Auditee,
Sum(IIf([tbl_Audits]![Audit_Type]="EXT",1,0)) AS External_Audits,
Sum(IIf([tbl_Audits]![Audit_Type]="INT",1,0)) AS Internal_Audits
FROM (((tbl_Programs LEFT JOIN
[qsel_Board_Finance_&_Audit_Committee_Sub1_Q2] ON tbl_Programs.Program_ID =
[qsel_Board_Finance_&_Audit_Committee_Sub1_Q2].Program_ID) INNER JOIN
tbl_Projects ON tbl_Programs.Program_ID = tbl_Projects.Program_ID) INNER JOIN
tbl_Work_Packages ON tbl_Projects.Project_ID = tbl_Work_Packages.Project_ID)
INNER JOIN tbl_Audits ON tbl_Work_Packages.Work_Package_ID =
tbl_Audits.Work_Package_ID
GROUP BY tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Name, Switch([Program_Code] In
("ECRL","CWAY-A","CWAY-B","NSSU"),"Attachment
3",[Program_Code]="TIDC","TIDC",True,[Supplier]), tbl_Audits.Carried_Out,
tbl_Programs.Excluded
HAVING (((tbl_Audits.Carried_Out)=True) AND ((tbl_Programs.Excluded)=No))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;


Please help me!~ Thanks a million!!!