Difference between Crosstab Query and Table Filters?

[Don S]

I have a crosstab query that selects data based on the contents of 5
fields. They aren't complex - simply state code (2 digits), territory
(0 or 1) etc.

When I run the crosstab query, I get different results than when I use
filter by selection with the identical selection criteria.

What am I missing? What resource should I study to become more
familar with the difference between these two approaches?

I am using Access 2003 but the database is in Access 2000 format (I
didn't convert it).

Thanks!
Don S

 

[Michel Walsh]

Hi,

A crosstab query generates NEW FIELDS from actual data: the new fields,
horizontally, come from data values (whatever expression you typed after the
word PIVOT in the crosstab query). Since a crosstab also re_group the
fields, the crosstab has generally less record than the initial table, as
does a standard Total query.

A filter use only existing fields, does not create new field. As example, it
would pick only the records that have the value "Seattle" under the field
City.


If the PIVOT is the field City, as example, for the crosstab, then you will
have a new field called Seattle, as example, another one called Paris, and
so on.


A filter would not change the structure of the table, at all, while a
crosstab creates a lot of new fields, eventually.



Hoping it may help,
Vanderghast, Access MVP

 

[Don S]

Thanks Michel,

I don't write SQL. I use design view for queries. Here is my SQL
code:

TRANSFORM Sum(tblLOB_MGA.ALAE) AS [The Value]
SELECT tblLOB_MGA.AY, Sum(tblLOB_MGA.ALAE) AS [Total Of ALAE]
FROM tblLOB_MGA
WHERE (((tblLOB_MGA.MAJORPERIL) Like "25*") AND ((tblLOB_MGA.DAC)="1") AND ((tblLOB_MGA.TYPE)="6") AND ((tblLOB_MGA.COMPANY)="5") AND ((tblLOB_MGA.STATE)="42") AND ((tblLOB_MGA.TERRITORY)="1") AND ((tblLOB_MGA.Legal)="1"))
GROUP BY tblLOB_MGA.AY
PIVOT (([AcctYR]-[AY])*4)+[AQ];

I realize I will have fewer rows of data, but what bothers me is the
sum of my crosstab query results (ie: sum of Loss") does not equal the
sum of the same field when I filter the table.

Thanks,
Don

 

[Michel Walsh]

Hi,


In a crosstab, the SUM occurs on the records that belong to the same GROUP,
but only of these records supplying also the same column, in your case,
those giving the same (([AcctYR]-[AY])*4)+[AQ].






You can get an 'horizontal sum', across the many fields, by adding SUM(
[The Value] ) :


TRANSFORM ...
SELECT tblLOB_MGA.AY,
Sum(tblLOB_MGA.ALAE) AS [Total Of ALAE],
Sum( [The Value] ) As TotalOfTheGroup
FROM ...


and that sum should be the same you would get under a Total query (with the
same WHERE clause, same GROUP BY)




Hoping it may help,
Vanderghast, Access MVP

Thanks Michel,

I don't write SQL. I use design view for queries. Here is my SQL
code:

TRANSFORM Sum(tblLOB_MGA.ALAE) AS [The Value]
SELECT tblLOB_MGA.AY, Sum(tblLOB_MGA.ALAE) AS [Total Of ALAE]
FROM tblLOB_MGA
WHERE (((tblLOB_MGA.MAJORPERIL) Like "25*") AND ((tblLOB_MGA.DAC)="1") AND
((tblLOB_MGA.TYPE)="6") AND ((tblLOB_MGA.COMPANY)="5") AND
((tblLOB_MGA.STATE)="42") AND ((tblLOB_MGA.TERRITORY)="1") AND
((tblLOB_MGA.Legal)="1"))
GROUP BY tblLOB_MGA.AY
PIVOT (([AcctYR]-[AY])*4)+[AQ];

I realize I will have fewer rows of data, but what bothers me is the
sum of my crosstab query results (ie: sum of Loss") does not equal the
sum of the same field when I filter the table.

Thanks,
Don

Hi,

A crosstab query generates NEW FIELDS from actual data: the new fields,
horizontally, come from data values (whatever expression you typed after
the
word PIVOT in the crosstab query). Since a crosstab also re_group the
fields, the crosstab has generally less record than the initial table, as
does a standard Total query.

A filter use only existing fields, does not create new field. As example,
it
would pick only the records that have the value "Seattle" under the field
City.


If the PIVOT is the field City, as example, for the crosstab, then you
will
have a new field called Seattle, as example, another one called Paris, and
so on.


A filter would not change the structure of the table, at all, while a
crosstab creates a lot of new fields, eventually.



Hoping it may help,
Vanderghast, Access MVP