Subquery???

[normanf]

Hi there,

The Database records Department, Wastage, Type,
Description, and Treatment(a Flag which is set to either 0
or -1), by Department.
total Departments = 17.

Only some departments have reportedly wastage of treatment
= 0. total Departments effected = 12.

Only some departments have reportedly wastage of treatment
= -1. Total Departments effected = 2.

I need a query that gives me the wastage, type and
description for treatment= 0 and wastage, type and
description for treatment = -1, for all departments as
datasource for a report.

Meaning those departments that have zero waste should show
zero and those who have wastage show what was wasted for
both treatment 0 and -1.

If I link on department where results of treatment = 0
with department results of treatment = -1, I only can
match on records that are included in the results of
treatment = 0. Where they aren't matching, the report will
fall short of those data.

Here is and Example:
SELECT TblAHSTypes.Department, TblWastedMaterial.Reason,
TlkupWastageIssues.ProblemCategory,
TblWastedMaterial.Number, TlkupWastageIssues.Type
FROM (TblAHSTypes LEFT JOIN TblWastedMaterial ON
TblAHSTypes.Department = TblWastedMaterial.Department)
LEFT JOIN TlkupWastageIssues ON TblWastedMaterial.Reason =
TlkupWastageIssues.WastIssuedescription
WHERE (((TblWastedMaterial.Treatment)=0))
GROUP BY TblAHSTypes.Department, TblWastedMaterial.Reason,
TlkupWastageIssues.ProblemCategory,
TblWastedMaterial.Number, TlkupWastageIssues.Type
HAVING (((TlkupWastageIssues.Type)="B"));

12 Departments are affected and 93 issues.

SELECT TblAHSTypes.Department, TblWastedMaterial.Reason,
TlkupWastageIssues.ProblemCategory,
TblWastedMaterial.Number, TlkupWastageIssues.Type
FROM (TblAHSTypes LEFT JOIN TblWastedMaterial ON
TblAHSTypes.Department = TblWastedMaterial.Department)
LEFT JOIN TlkupWastageIssues ON TblWastedMaterial.Reason =
TlkupWastageIssues.WastIssuedescription
WHERE (((TblWastedMaterial.Treatment)=-1))
GROUP BY TblAHSTypes.Department, TblWastedMaterial.Reason,
TlkupWastageIssues.ProblemCategory,
TblWastedMaterial.Number, TlkupWastageIssues.Type
HAVING (((TlkupWastageIssues.Type)="B"));

2 departments affected and 6 issues.

any suggestion????

Kind Regards

Norman

 

[Gerald Stanley]

Try something along the lines of
SELECT DISTINCT TblAHSTypes.Department,
TblWastedMaterial.Reason,
TlkupWastageIssues.ProblemCategory,
TblWastedMaterial.Number,
TlkupWastageIssues.Type,
TblWastedMaterial.Treatment
FROM (TblAHSTypes LEFT JOIN TblWastedMaterial ON
TblAHSTypes.Department = TblWastedMaterial.Department)
LEFT JOIN TlkupWastageIssues ON TblWastedMaterial.Reason =
TlkupWastageIssues.WastIssuedescription
WHERE (((TlkupWastageIssues.Type)="B"))
UNION
SELECT DINSTINCT Department, "", 0, 0, "", 1
FROM TblAHSTypes
WHERE Department NOT IN (SELECT DISTINCT Department FROM
TblWastedMaterial)

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

thanks for your hint.

It works well!!!!

regards Norman