crosstab & pivot table give different result

[Guest]

I am creating a report that shows crosstab figures. To double check the
correctness, I also created a pivot table based on the same query as crosstab
uses. Then I get slightly different values from these two tables, the
difference seemingly bigger than simply the matter of rounding or truncation.
I don't understand why this happens, and wonder if someone have experienced
this before. The following is the SQL for the cross tab.

TRANSFORM Sum([transaction].bought) AS SumOf bought
SELECT [transaction].[_product], [transaction]._country_of_origin
FROM [transaction]
GROUP BY [transaction].[_product], [transaction]._country_of_origin
PIVOT [transaction].[_reporting_year] In ("2002","2003","2004","2005","2006");

and the following is the SQL for query that is the base for pivot table.

SELECT [transaction].[_product], [transaction]._bought,
[transaction].[_reporting_year], [transaction]._country_of_origin
FROM [transaction];


I will appreciate greatly for your suggestions and insights.
Thanks,

 

[Duane Hookom]

I would start limiting the records in both queries to see which records
might be affecting the outputs.

Duane Hookom
MS Access MVP

 

[Guest]

You did not say but I would guess that the crosstab is smaller as the years
will limit the output.

 

[Guest]

Thank you for your reply & suggestions, Duane & Karl,

I compared the base numbers that are used for pivot tabel and cross tab. It
seems pivot table truncates the number at decimal point wheares cross tab do
not.
The original data is defined as numbers with 2 decimal points. I changed the
format of this particular column in both design and pivot table mode, but
still the numbers in the column in pivot table do not show any values below
decimal point except 00. (It does show 2 decimal points when I open in
datasheet mode and check the individual base numbers.)
Since I was using pivot table just for double checking the numbers, it is
not critical for me to fix this problem.
Still I wonder if I overlooked some other ways to get pivot table display 2
decimal accuracy.
Thank you any ways for your help!

I would start limiting the records in both queries to see which records
might be affecting the outputs.

Duane Hookom
MS Access MVP

I am creating a report that shows crosstab figures. To double check the
correctness, I also created a pivot table based on the same query as
crosstab
uses. Then I get slightly different values from these two tables, the
difference seemingly bigger than simply the matter of rounding or
truncation.
I don't understand why this happens, and wonder if someone have
experienced
this before. The following is the SQL for the cross tab.

TRANSFORM Sum([transaction].bought) AS SumOf bought
SELECT [transaction].[_product], [transaction]._country_of_origin
FROM [transaction]
GROUP BY [transaction].[_product], [transaction]._country_of_origin
PIVOT [transaction].[_reporting_year] In
("2002","2003","2004","2005","2006");

and the following is the SQL for query that is the base for pivot table.

SELECT [transaction].[_product], [transaction]._bought,
[transaction].[_reporting_year], [transaction]._country_of_origin
FROM [transaction];


I will appreciate greatly for your suggestions and insights.
Thanks,