Crosstab query

  • Thread starter Thread starter Sandy
  • Start date Start date
S

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
 
You can hard-code column names into the Column Headings property.
Column Headings:"2003D","2003A","2003M","2004D","2004A","2004M"
 
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
Click to expand...
Headings:"2003D","2003A","2003M","2004D","2004A","2004M"

--
Duane Hookom
MS Access MVP


Sandy said:
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
Click to expand...


.
Click to expand...
 
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


Sandy said:
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
Click to expand...
Headings:"2003D","2003A","2003M","2004D","2004A","2004M"

--
Duane Hookom
MS Access MVP


Sandy said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top