A query based on another queries

[Liliane]

I have a query (named qry_Safety_ITSRR_Sub1) that bases on another two
queries. Those two queries are all right when they're run seperately. But
when I run the qry_Safety_ITSRR_Sub1, one calculation field
(Safety_Total_Audits) initially included in qry_ITSRR_Report_Sub2_1 gives me
wrong results. Could you please help me on this. Thanks a million!!!!!
There're the queries:

qry_ITSRR_Report_Sub2_1:
SELECT Switch([tbl_Audit_Categories.Audit_Category] In ("Env
CoA","Environment"),"Environment (Including Env
CoA)",[tbl_Audit_Categories.Audit_Category] In ("OHS","OHS & RS","Rail
Safety"),"Safety (OHS & Rail
Safety)",True,[tbl_Audit_Categories.Audit_Category]) AS [Audit Category],
Sum(IIf(([Report_Received]<=forms!frmReports!ToDate) And
([Report_Received]>=forms!frmReports!FromDate) And ([OHS]=True Or
[rail_safety]=True),1,0)) AS Safety_Total_Audits, tbl_Audits.Work_Package_ID,
tbl_Audits.Audit_No
FROM tbl_Audit_Categories LEFT JOIN tbl_Audits ON
tbl_Audit_Categories.Audit_Category=tbl_Audits.Audit_Category
GROUP BY Switch([tbl_Audit_Categories.Audit_Category] In ("Env
CoA","Environment"),"Environment (Including Env
CoA)",[tbl_Audit_Categories.Audit_Category] In ("OHS","OHS & RS","Rail
Safety"),"Safety (OHS & Rail
Safety)",True,[tbl_Audit_Categories.Audit_Category]),
tbl_Audits.Work_Package_ID, tbl_Audits.Audit_No;

qry_Safety_Related_ITSRR_Report_Sub1_1:
SELECT Sum(IIf(([Report_Received]>=forms!frmReports!FromDate) And
([Report_Received]<=forms!frmReports!ToDate) And ([Item_Category]="OHS" Or
[Item_Category]="Rail Safety"),1,0)) AS Total_Audits_Findings,
Sum(IIf(([Report_Received]<=forms!frmReports!ToDate) And
([Report_Received]>=forms!frmReports!FromDate) And ([Item_Category]="OHS" Or
[Item_Category]="Rail Safety") And ([Date_Actioned] Is Not Null),1,0)) AS
Audits_Closed, Sum(IIf(([Report_Received]<=forms!frmReports!ToDate) And
([Report_Received]>=forms!frmReports!FromDate) And ([Item_Category]="OHS" Or
[Item_Category]="Rail Safety") And ([Date_Actioned] Is Null),1,0)) AS
Audits_Pending, Sum(IIf(([Report_Received]<=forms!frmReports!ToDate) And
([Report_Received]>=forms!frmReports!FromDate) And ([Item_Category]="OHS" Or
[Item_Category]="Rail Safety") And IsNull([Date_Actioned]) And
([When]<[Status_Date]) And [Item_No]<>"00",1,0)) AS Audits_Overdue,
tbl_Audits.Audit_No, tbl_Audits.Work_Package_ID
FROM tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No
GROUP BY tbl_Audits.Audit_No, tbl_Audits.Work_Package_ID;

qry_Safety_ITSRR_Sub1
SELECT tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label,
Sum(qry_ITSRR_Report_Sub2_1.Safety_Total_Audits) AS Safety_Total_Audits,
Sum(qry_Safety_Related_ITSRR_Report_Sub1_1.Total_Audits_Findings) AS
Safety_Total_Audits_Findings,
Sum(qry_Safety_Related_ITSRR_Report_Sub1_1.Audits_Closed) AS
Safety_Audits_Closed,
Sum(qry_Safety_Related_ITSRR_Report_Sub1_1.Audits_Pending) AS
Safety_Audits_Pending,
Sum(qry_Safety_Related_ITSRR_Report_Sub1_1.Audits_Overdue) AS
Safety_Audits_Overdue
FROM qry_Safety_Related_ITSRR_Report_Sub1_1 INNER JOIN ((tbl_Programs INNER
JOIN tbl_Projects ON tbl_Programs.Program_ID = tbl_Projects.Program_ID) INNER
JOIN (tbl_Work_Packages INNER JOIN qry_ITSRR_Report_Sub2_1 ON
tbl_Work_Packages.Work_Package_ID = qry_ITSRR_Report_Sub2_1.Work_Package_ID)
ON tbl_Projects.Project_ID = tbl_Work_Packages.Project_ID) ON
qry_Safety_Related_ITSRR_Report_Sub1_1.Audit_No =
qry_ITSRR_Report_Sub2_1.Audit_No
GROUP BY tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label;


Thanks again!!!

 

[Lord Kelvan]

ok without reading thoes nice long queries, you are telling me it
gives you wrong results what are the wrong results or how are they
wrong