Crosstab query

[Sandy]

Hi
I have a cross tab query that I am trying to use to
produce a report as below

2003 2004
Company D A M D A M
----------------------------------------
Bakers Ltd 5 11 2 3 15 0
Abbots Ltd 1 5 3 16 2 0
etc.

My sql looks like this:

TRANSFORM Sum(qryRptBudgetSpan.Amount) AS [The Value]
SELECT qryRptBudgetSpan.AuthName, Sum
(qryRptBudgetSpan.Amount) AS [Total Of Amount]
FROM qryRptBudgetSpan
GROUP BY qryRptBudgetSpan.AuthName
PIVOT [Year] & [type];

My current sql produces results that are close to what I
want however, if one of the categories is 0 or null (as in
my last column in the report displayed above), I still
want the query to display this column. At the moment, null
fields are being ommitted completely and the category they
are in does not display.

Any suggestions on how to fix this would be greatly
appreciated.

Sandy

 

[Duane Hookom]

You can hard-code column names into the Column Headings property.
Column Headings:"2003D","2003A","2003M","2004D","2004A","2004M"

 

[Sandy]

Thanks for the tip Duane but in this query, the starting
value could be a different year each time. Any clues?

Sandy

-----Original Message-----
You can hard-code column names into the Column Headings property.

Column

Headings:"2003D","2003A","2003M","2004D","2004A","2004M"

--
Duane Hookom
MS Access MVP

Hi
I have a cross tab query that I am trying to use to
produce a report as below

2003 2004
Company D A M D A M
----------------------------------------
Bakers Ltd 5 11 2 3 15 0
Abbots Ltd 1 5 3 16 2 0
etc.

My sql looks like this:

TRANSFORM Sum(qryRptBudgetSpan.Amount) AS [The Value]
SELECT qryRptBudgetSpan.AuthName, Sum
(qryRptBudgetSpan.Amount) AS [Total Of Amount]
FROM qryRptBudgetSpan
GROUP BY qryRptBudgetSpan.AuthName
PIVOT [Year] & [type];

My current sql produces results that are close to what I
want however, if one of the categories is 0 or null (as in
my last column in the report displayed above), I still
want the query to display this column. At the moment, null
fields are being ommitted completely and the category they
are in does not display.

Any suggestions on how to fix this would be greatly
appreciated.

Sandy

.

 

[Duane Hookom]

I would use a "relative" year with either a parameter or a reference to a
year entered into a control on a form.

PARAMETERS [End Year] Integer;
TRANSFORM Sum(qryRptBudgetSpan.Amount) AS [The Value]
SELECT qryRptBudgetSpan.AuthName, Sum
(qryRptBudgetSpan.Amount) AS [Total Of Amount]
FROM qryRptBudgetSpan
GROUP BY qryRptBudgetSpan.AuthName
PIVOT [type] & [End Year] - [Year]
Column Headings:"D0","A0","M0","D1","A1","M1";


--
Duane Hookom
MS Access MVP

Thanks for the tip Duane but in this query, the starting
value could be a different year each time. Any clues?

Sandy

-----Original Message-----
You can hard-code column names into the Column Headings property.

Column

Headings:"2003D","2003A","2003M","2004D","2004A","2004M"

--
Duane Hookom
MS Access MVP

Hi
I have a cross tab query that I am trying to use to
produce a report as below

2003 2004
Company D A M D A M
----------------------------------------
Bakers Ltd 5 11 2 3 15 0
Abbots Ltd 1 5 3 16 2 0
etc.

My sql looks like this:

TRANSFORM Sum(qryRptBudgetSpan.Amount) AS [The Value]
SELECT qryRptBudgetSpan.AuthName, Sum
(qryRptBudgetSpan.Amount) AS [Total Of Amount]
FROM qryRptBudgetSpan
GROUP BY qryRptBudgetSpan.AuthName
PIVOT [Year] & [type];

My current sql produces results that are close to what I
want however, if one of the categories is 0 or null (as in
my last column in the report displayed above), I still
want the query to display this column. At the moment, null
fields are being ommitted completely and the category they
are in does not display.

Any suggestions on how to fix this would be greatly
appreciated.

Sandy

.