|
 |
access thread: Access Report based on a Crosstab Query
Message #1 by "Fran Galway" <fgalway@r...> on Tue, 14 Aug 2001 12:49:26 -0500
|
|
I have a crosstab query that contains four columns: Division[GroupBy,
RowHeading], SalesRep[GroupBy, RowHeading], Status[GroupBy, ColumnHeading],
CountStatus[Expression, Value]. This query is based on another query that
consists of two tables SalesRepByDate and Status. The status table contains
a fixed list of the six possible status types. In order to ensure that all
six status types show up as columns in the crosstab query (even if that
status was not used), I made a Right Outer Join to ensure that all records
from the Status table were listed even if there was not a matching record in
the SalesRepByDate table.
Each time the report is run there are at least two statuses that have a
count of 0 which creates a row with the values: Division = Null, SalesRep
Null, and all six statuses are listed with a value of '0'. The report is
grouped by Division, so the first entry in the report is a null division
with a null SalesRep and zeroes in each of the status columns. Can anyone
give me an idea of how to test for the condition that if the Division is
Null, then the record is skipped so that record is not printed on the
report?
Thanks for any ideas (I've already used several hints from earlier posts to
create this report!)
Fran Galway
Ranger American
FGalway@r...
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Tue, 14 Aug 2001 14:41:41 -0700
|
|
Can you not use a WHERE clause in your base query, e.g., WHERE Division IS
NOT NULL?
HTH,
-Roy
-----Original Message-----
From: Fran Galway [mailto:fgalway@r...]
Sent: Tuesday, August 14, 2001 10:49 AM
To: Access
Subject: [access] Access Report based on a Crosstab Query
I have a crosstab query that contains four columns: Division[GroupBy,
RowHeading], SalesRep[GroupBy, RowHeading], Status[GroupBy, ColumnHeading],
CountStatus[Expression, Value]. This query is based on another query that
consists of two tables SalesRepByDate and Status. The status table contains
a fixed list of the six possible status types. In order to ensure that all
six status types show up as columns in the crosstab query (even if that
status was not used), I made a Right Outer Join to ensure that all records
from the Status table were listed even if there was not a matching record in
the SalesRepByDate table.
Each time the report is run there are at least two statuses that have a
count of 0 which creates a row with the values: Division = Null, SalesRep
Null, and all six statuses are listed with a value of '0'. The report is
grouped by Division, so the first entry in the report is a null division
with a null SalesRep and zeroes in each of the status columns. Can anyone
give me an idea of how to test for the condition that if the Division is
Null, then the record is skipped so that record is not printed on the
report?
Thanks for any ideas (I've already used several hints from earlier posts to
create this report!)
Fran Galway
Ranger American
FGalway@r...
Message #3 by tperkins@g... on Wed, 15 Aug 2001 09:18:07
|
|
Forget the right join and check out the Column Headings property in Query
Properties. You can force the use of columns in a particular order even if
no data is returned for that column.
Tim Perkins
> I have a crosstab query that contains four columns: Division[GroupBy,
> RowHeading], SalesRep[GroupBy, RowHeading], Status[GroupBy,
ColumnHeading],
> CountStatus[Expression, Value]. This query is based on another query
that
> consists of two tables SalesRepByDate and Status. The status table
contains
> a fixed list of the six possible status types. In order to ensure that
all
> six status types show up as columns in the crosstab query (even if that
> status was not used), I made a Right Outer Join to ensure that all
records
> from the Status table were listed even if there was not a matching
record in
> the SalesRepByDate table.
>
> Each time the report is run there are at least two statuses that have a
> count of 0 which creates a row with the values: Division = Null,
SalesRep
> Null, and all six statuses are listed with a value of '0'. The report is
> grouped by Division, so the first entry in the report is a null division
> with a null SalesRep and zeroes in each of the status columns. Can
anyone
> give me an idea of how to test for the condition that if the Division is
> Null, then the record is skipped so that record is not printed on the
> report?
>
> Thanks for any ideas (I've already used several hints from earlier posts
to
> create this report!)
>
> Fran Galway
> Ranger American
> FGalway@r...
>
>
|
|
|
View Cart
