Two queueries - same table - different totals

[Love Buzz]

Hi all.

OK, I have two queuries that are pulling from the same table. One is a
Summary and one is a subtotal of the queues that make up the type. The
subtotal query is lower than the summary query. Any idea why?

Thanks for your help. The two queuries:

BY Type - Summary:
SELECT [Incoming Return Item Detail].Type, Count([Incoming Return Item
Detail].Site) AS CountOfSite
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [StartDate] And
[EndDate] And ([Incoming Return Item Detail].Date) Between [StartDate] And
[EndDate] And ([Incoming Return Item Detail].Date) Between [StartDate] And
[EndDate]))
GROUP BY [Incoming Return Item Detail].Type;

By Type/Queu:
SELECT [Incoming Return Item Detail].[Type], [Incoming Return Item
Detail].[queue], Count([Incoming Return Item Detail].[queue]) AS
CountOfqueue, [Queues and Descriptions].[Queue Name]
FROM [Incoming Return Sources] INNER JOIN ([Incoming Return Item Detail]
INNER JOIN [Queues and Descriptions] ON [Incoming Return Item
Detail].[queue]=[Queues and Descriptions].[Queue]) ON [Incoming Return
Sources].[Source]=[Incoming Return Item Detail].[Source]
WHERE ((([Incoming Return Item Detail].[Date]) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].[Type], [Incoming Return Item
Detail].[queue], [Queues and Descriptions].[Queue Name]
HAVING ((([Incoming Return Item Detail].Type) Like [What Type] & "*"));

 

[Rob Parker]

Your second query contains an inner join; this will only return records from
the Incoming Return Item Detail table where the Queue field in both tables
in the query matches, it will not show any records where there is a Queue
entry with no match in the Queues and Descriptions table and (more likely to
be the problem) it will not show any records where Queue is null. Change
the join to an outer join (Left or Right) by clicking on the join in the
query design grid, and selecting "Show all records from Incoming Return Item
Detail table ...".

I'm assuming that you are not filtering the second query by entering
something in the What Type prompt field, which would, naturally, limit the
records returned.

HTH,

Rob