Duplicated results

[Liliane]

Hi,

I'm writing a query. It was fine with grouping by Programs, but now I need
to add another category group -- Audit_Group, I got results duplicated.

The SQL is:

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled]))
AS Items_Sampled, qry_Monthly_Report_Sub1_Q1.Items_Re_Opened,
Count(qry_Reopened_Items.Audit_No) AS CountOfAudit_No
FROM (((tbl_Programs INNER JOIN qry_Monthly_Report_Sub1_Q1 ON
tbl_Programs.Program_ID = qry_Monthly_Report_Sub1_Q1.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 LEFT JOIN qry_Reopened_Items ON tbl_Audits.Audit_No =
qry_Reopened_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_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled])),
qry_Monthly_Report_Sub1_Q1.Items_Re_Opened, tbl_Audits.Carried_Out
HAVING (((tbl_Programs.Program_ID)<>"08") AND ((tbl_Audits.Carried_Out)=Yes))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;


Currently, the results I get are (The last 3 lines should be 0):

Audit_Group Program_ID Items_Sampled Items_Re_Opened
Strategic 10 9 3
Performance 01 444 33
Performance 02 771 105
Performance 04 87 5
Performance 07 0 0
Performance 10 (9) (3)
Operational 10 (9) (3)
Certification 10 (9) (3)


Please help me!~

Many thanks!!!!

 

[Michel Walsh]

And why the last three lines "should" be zero? and what do you mean by to be
"zero" ?


Your query, as it is, just display the DIFFERENT groups. If you want to
remove some groups from the result, use the HAVING clause (if that involves
an aggregate, like SUM, COUNT, ....) or the WHERE clause (if that does not
involve an aggregate).



Vanderghast, Access MVP

 

[Liliane]

We did a manual calculation, that's why we know the last 3 lines should be 0
instead of 9.

The numbers are from this query (qry_Monthly__Report_Sub1_Q1):

SELECT tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label,
Sum(IIf(IsNull([tbl_Verifications.Items_Sampled]),0,[tbl_Verifications.Items_sampled]))
AS Items_Sampled,
Sum(IIf(IsNull([tbl_Verifications.Items_Re_Opened]),0,[tbl_Verifications.Items_Re_Opened])) AS Items_Re_Opened
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) LEFT JOIN
tbl_Verifications ON tbl_Work_Packages.Work_Package_ID =
tbl_Verifications.Work_Package_ID
WHERE (((tbl_Programs.Excluded)=No))
GROUP BY tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label
ORDER BY tbl_Programs.Program_ID;

Program_ID Program_Report_Label Items_Sampled
01 ECRL 444
02 Clearways A 771
04 North Sydney 87
07 SWRL 0
10 TIDC 9

This query gives me the right answers grouped by Program_ID, but I need the
numbers grouped by both Program_ID and Audit_Group. And I got the duplicated
results.

Cheers.

And why the last three lines "should" be zero? and what do you mean by to be
"zero" ?


Your query, as it is, just display the DIFFERENT groups. If you want to
remove some groups from the result, use the HAVING clause (if that involves
an aggregate, like SUM, COUNT, ....) or the WHERE clause (if that does not
involve an aggregate).



Vanderghast, Access MVP

Hi,

I'm writing a query. It was fine with grouping by Programs, but now I
need
to add another category group -- Audit_Group, I got results duplicated.

The SQL is:

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled]))
AS Items_Sampled, qry_Monthly_Report_Sub1_Q1.Items_Re_Opened,
Count(qry_Reopened_Items.Audit_No) AS CountOfAudit_No
FROM (((tbl_Programs INNER JOIN qry_Monthly_Report_Sub1_Q1 ON
tbl_Programs.Program_ID = qry_Monthly_Report_Sub1_Q1.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 LEFT JOIN qry_Reopened_Items ON tbl_Audits.Audit_No
=
qry_Reopened_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_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled])),
qry_Monthly_Report_Sub1_Q1.Items_Re_Opened, tbl_Audits.Carried_Out
HAVING (((tbl_Programs.Program_ID)<>"08") AND
((tbl_Audits.Carried_Out)=Yes))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;


Currently, the results I get are (The last 3 lines should be 0):

Audit_Group Program_ID Items_Sampled Items_Re_Opened
Strategic 10 9 3
Performance 01 444 33
Performance 02 771 105
Performance 04 87 5
Performance 07 0 0
Performance 10 (9) (3)
Operational 10 (9) (3)
Certification 10 (9) (3)


Please help me!~

Many thanks!!!!

 

[Michel Walsh]

If you use 3 fields (or expression) in the GROUP BY clause, then you get all
different TRIPLET, without duplicated TRIPLET. If you add a fourth field
(expression) to the GROUP BY clause, then the group/no duplicated is based
on the QUARTET (the four fields): [alpha, beta, gamma, 5] does not
duplicate (is a different group than) [alpha, beta, omega, 5] since the
third value in the twos quartet *is* different.

If SIMPLY addding a new expression in the GROUP BY clause crashes your
result (from what I understand, that is what is happening to you), then,
maybe, your problem is to UNION two queries,:


SELECT ... FROM ... GROUP BY f1, f2, f3
UNION ALL
SELECT ... FROM ... GROUP BY f5, f2, f3


as example.


Maybe I don't understand your problem, though. I based my understanding on
your introduction sentence in your first message:

" I'm writing a query. It was fine with grouping by Programs, "

so, I assume you were not having duplicated values at that point.


So, UNLESS you ALSO modified the FROM clause by adding a new table, I fail
to see why you would get duplicated values, just by adding an additional
field in the GROUP BY clause, and by duplicated, I use the SQL meaning of
'duplicated', which may not be what your intention is.



Vanderghast, Access MVP

We did a manual calculation, that's why we know the last 3 lines should be
0
instead of 9.

The numbers are from this query (qry_Monthly__Report_Sub1_Q1):

SELECT tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label,
Sum(IIf(IsNull([tbl_Verifications.Items_Sampled]),0,[tbl_Verifications.Items_sampled]))
AS Items_Sampled,
Sum(IIf(IsNull([tbl_Verifications.Items_Re_Opened]),0,[tbl_Verifications.Items_Re_Opened]))
AS Items_Re_Opened
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) LEFT JOIN
tbl_Verifications ON tbl_Work_Packages.Work_Package_ID =
tbl_Verifications.Work_Package_ID
WHERE (((tbl_Programs.Excluded)=No))
GROUP BY tbl_Programs.Program_ID, tbl_Programs.Program_Report_Label
ORDER BY tbl_Programs.Program_ID;

Program_ID Program_Report_Label Items_Sampled
01 ECRL 444
02 Clearways A 771
04 North Sydney 87
07 SWRL 0
10 TIDC 9

This query gives me the right answers grouped by Program_ID, but I need
the
numbers grouped by both Program_ID and Audit_Group. And I got the
duplicated
results.

Cheers.

And why the last three lines "should" be zero? and what do you mean by to
be
"zero" ?


Your query, as it is, just display the DIFFERENT groups. If you want to
remove some groups from the result, use the HAVING clause (if that
involves
an aggregate, like SUM, COUNT, ....) or the WHERE clause (if that does
not
involve an aggregate).



Vanderghast, Access MVP

Hi,

I'm writing a query. It was fine with grouping by Programs, but now I
need
to add another category group -- Audit_Group, I got results duplicated.

The SQL is:

SELECT tbl_Audits.Audit_Group, tbl_Programs.Program_ID,
tbl_Programs.Program_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled]))
AS Items_Sampled, qry_Monthly_Report_Sub1_Q1.Items_Re_Opened,
Count(qry_Reopened_Items.Audit_No) AS CountOfAudit_No
FROM (((tbl_Programs INNER JOIN qry_Monthly_Report_Sub1_Q1 ON
tbl_Programs.Program_ID = qry_Monthly_Report_Sub1_Q1.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 LEFT JOIN qry_Reopened_Items ON
tbl_Audits.Audit_No
=
qry_Reopened_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_Report_Label,
(IIf(IsNull([qry_Monthly_Report_Sub1_Q1]![Items_Sampled]),0,[qry_Monthly_Report_Sub1_Q1]![Items_Sampled])),
qry_Monthly_Report_Sub1_Q1.Items_Re_Opened, tbl_Audits.Carried_Out
HAVING (((tbl_Programs.Program_ID)<>"08") AND
((tbl_Audits.Carried_Out)=Yes))
ORDER BY tbl_Audits.Audit_Group DESC , tbl_Programs.Program_ID;


Currently, the results I get are (The last 3 lines should be 0):

Audit_Group Program_ID Items_Sampled Items_Re_Opened
Strategic 10 9 3
Performance 01 444 33
Performance 02 771 105
Performance 04 87 5
Performance 07 0 0
Performance 10 (9) (3)
Operational 10 (9) (3)
Certification 10 (9) (3)


Please help me!~

Many thanks!!!!