A query based on another query

L

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!!!
 
D

Dale Fye

Liliane,

How do you know that [Safety_Total_Audits] is returning "wrong results".

My speculation is that the INNER JOINs you are using to join the various
elements of your query (qry_Safety_ITSRR_Sub1) are resulting in the "lost
records".

This would imply that you have records in tbl_Audits, tbl_Audit_Categories,
or tbl_AAL_Items (the sources for your top two queries) that do not match up.

What you might want to do is something like:
1. Create an unmatched query from query 1 to query2 using the Audit_no field
2. Create another unmatched query from query2 to query1 using the Audit_no
field

These should help you identify where you are "losing" records.

Another way might be to change the INNER JOINs in the last query to
LeftJoins. If you then get rid of the totals query and make it a pure SELECT
query, you should also be able to identify where the joins are not matched.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Liliane said:
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!!!
Click to expand...
 
L

Liliane

Dale,

I changed the Inner Join to Left Join. It's all right now.

Thank you again.

Liliane



Dale Fye said:
Liliane,

How do you know that [Safety_Total_Audits] is returning "wrong results".

My speculation is that the INNER JOINs you are using to join the various
elements of your query (qry_Safety_ITSRR_Sub1) are resulting in the "lost
records".

This would imply that you have records in tbl_Audits, tbl_Audit_Categories,
or tbl_AAL_Items (the sources for your top two queries) that do not match up.

What you might want to do is something like:
1. Create an unmatched query from query 1 to query2 using the Audit_no field
2. Create another unmatched query from query2 to query1 using the Audit_no
field

These should help you identify where you are "losing" records.

Another way might be to change the INNER JOINs in the last query to
LeftJoins. If you then get rid of the totals query and make it a pure SELECT
query, you should also be able to identify where the joins are not matched.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Liliane said:
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!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A query based on another queries 1
Right records on a query but doubling up when summing them 2
Data Type mismatch in a query that uses a calculated field from another query 4
Jet error running crosstab query based on select query 1
Filtering duplicate participants from query based on a study name 1
open a subform or hide it based on query results 15

Top