CrossTab query Count() doesn't match.

[elena]

Hi All,
I really need help to figure out why Crosstab query results doesn't match another regular query result with the same parameters.
I am using date range for example 1/1/2011 - 4/30/2014 and the return result looks like following (reports, database was created in Access 2003 and now I am open it in 2010), but if I change date range to
just 4/1/14 - 5/1/14 then I still see the difference:

Badge Valid Void Total Ticks Total Amount
100 29 0 29 $345.00
105 5 0 5 $75.00

crosstab SQL statement;

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(Master.TickNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(Master.AmtVl) AS SumOfAmount, Nz(COUNT(Master.TickNo),0) AS TotalTicks
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([DisposCode]="VD","Void","Valid") IN ("Void","Valid");


another query SQL statement the return result below I am using the same date range and Badge "105" and the totals are completely different;

Viol/Code Violation Description Fine Tickets Total Amount
101 Meter Violation $100.00 117 $11,500.00


.... ................. ....... .... .......

Totals: 12538 $206,365.00

SELECT Master.Viol, Violat.Description, Violat.Fine, COUNT(Master.TickNo) AS TotalTickets, Sum(Master.AmtVl) AS SumOfAmount
FROM Master INNER JOIN Violat ON Master.Viol=Violat.Viol
WHERE (((Master.IssDate) Between [Enter start Date] And [Enter end Date]))
GROUP BY Master.Viol, Violat.Description, Violat.Fine
ORDER BY Master.Viol, Violat.Description, Violat.Fine;

There is 2 tables "Master" and "Violat" in the database.
Can somebody help?
thank you,

[June7]

The SELECT results are correct?

Want to provide db for analysis? Follow instructions at bottom of my post.

[pbaldy]

The extra table in the second query could potentially change the result. With the join you've specified, only records existing in both tables would be returned. The first would pull everything from the master table that meets the criteria.

[elena]

Hi pbaldy,
That is the problem that the crosstab which is source is only one table "Master" returns only 5 tickets and the second query (has INNER JOIN) returns 12538 tickets as total. This is crazy to compare 5 and 12538 for the same date range.

[pbaldy]

Are there that many records in the second table? Is there a date field in that table that should have criteria on it?

[elena]

Hi pbaldy,
I am comparing reports or return result directly on the query (by double clicking on the query and entering date range parameters).
"Master" table has a lot of records 127131 that is the base for the crosstab. Table "Violat" has only 11 records and it is look up table where Violation code, description and Fine (currency).
Second query has INNER JOIN on both tables and this specific query returns most accurate result. I am using INNER JOIN in the query only for the report will look nice.

SELECT Master.Viol, Violat.Description, Violat.Fine, COUNT(Master.TickNo) AS TotalTickets, Sum(Master.AmtVl) AS SumOfAmount
FROM Master INNER JOIN Violat ON Master.Viol=Violat.Viol
WHERE (((Master.IssDate) Between [Enter start Date] And [Enter end Date]))
GROUP BY Master.Viol, Violat.Description, Violat.Fine
ORDER BY Master.Viol, Violat.Description, Violat.Fine