Your issue is in the from statement of the query.
Code:
SELECT DISTINCT DocumentType
,TrackingNo
FROM [ReportLog] AS [%$##@_Alias]
,tblMain
INNER JOIN [ReportLog]
ON tblMain.TrackingNo = [ReportLog].TrackingNo
WHERE (
(
(tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
AND [Forms] ! [frmProductTotals] ! [txtEndDate]
)
)
There is a cross join occurring. ReportLog occurs twice in your query and once it is not joined to anything--creating duplicate records for every entry that occurs in ReportLog. It is listed once as [%$##@_Alias] and once as [ReportLog].
Rewrite this portion of the query as:
Code:
SELECT DISTINCT DocumentType
,TrackingNo
FROM tblMain
INNER JOIN [ReportLog]
ON tblMain.TrackingNo = [ReportLog].TrackingNo
WHERE (
(
(tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
AND [Forms] ! [frmProductTotals] ! [txtEndDate]
)
)
And ultimately reinsert it into your aggregation as:
Code:
SELECT DocumentType
,Count(TrackingNo) AS CountOfTrackingNo
FROM (
SELECT DISTINCT DocumentType
,TrackingNo
FROM tblMain
INNER JOIN [ReportLog]
ON tblMain.TrackingNo = [ReportLog].TrackingNo
WHERE (
(
(tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
AND [Forms] ! [frmProductTotals] ! [txtEndDate]
)
)
)
GROUP BY DocumentType;
Cheers,
Jeff