Symptoms

• Circulation Transactions by Dewey Decimal Classification report returns abnormally high number
• Due to the way MFHD_ITEM table is included, for each item charged, query counts number of items attached to item's MFHD, instead of counting 1

Defect Status

Issue 16384-98 is currently in Development.

Replication Steps

Query as delivered in reports.mdb

SELECT DEWEYCLASS_VW.CLASS AS Expr1, [start date] AS StartDate, [end date] AS EndDate, Count(CIRCCHARGES_VW.CHARGE_DATE_ONLY) AS CountOfCHARGE_DATE_ONLY
FROM DEWEYCLASS_VW, MFHD_ITEM, CIRCCHARGES_VW
WHERE ((([CIRCCHARGES_VW].[CHARGE_DATE_ONLY]) Between [start date] And [end date]))
GROUP BY DEWEYCLASS_VW.CLASS, [start date], [end date];

Workaround

Use this query instead

SELECT DEWEYCLASS_VW.CLASS, [start date] AS StartDate, [end date] AS EndDate,
Count(CIRCCHARGES_VW.CHARGE_DATE_ONLY) AS CountOfCHARGE_DATE_ONLY
FROM CIRCCHARGES_VW INNER JOIN DEWEYCLASS_VW ON CIRCCHARGES_VW.MFHD_ID = DEWEYCLASS_VW.MFHD_ID
WHERE (((CIRCCHARGES_VW.CHARGE_DATE_ONLY) Between [start date] And [end date]))
GROUP BY DEWEYCLASS_VW.CLASS, [start date], [end date];

• Article last edited: 08-Oct-2013