Crosstab - if no data, then no heading

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Whenever I run my query and there is no data for a specific month the
crosstab will skip that month and show the next that contains data (for
example if 200309 has no data then there is no column displayed for 200309).
How can I set it up so that the column heading will still display even if
there is no data for that month?

Any help is greatly appreciated!!

PARAMETERS [Forms]![Form_Main]![GroupName] Text ( 255 );
TRANSFORM Sum(dbo_EHP_OutpatientVisits.PaidAmt) AS SumOfPaidAmt
SELECT Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
FROM (Sub_Group_Information INNER JOIN Group_Information ON
Sub_Group_Information.GroupID = Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Form_Main]![GroupName])) OR
(((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
(([Forms]![Form_Main]![GroupName])="ALL"))
GROUP BY Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
PIVOT dbo_EHP_OutpatientVisits.PaidMonth;
 
You can "force" a column by entering all possible column headings into the
Column Headings property of your crosstab query.
 
Hi Duane,

Thanks so much for your quick reply. Unfortunately, if I were to force a
column heading I would have to do it nearly 50 times since my data goes back
to 200301, and I would have to add another column each month. Do you have
any other suggestions?

I really appreciate your help, and thanks again.

Duane Hookom said:
You can "force" a column by entering all possible column headings into the
Column Headings property of your crosstab query.

--
Duane Hookom
Microsoft Access MVP


shorticake said:
Whenever I run my query and there is no data for a specific month the
crosstab will skip that month and show the next that contains data (for
example if 200309 has no data then there is no column displayed for 200309).
How can I set it up so that the column heading will still display even if
there is no data for that month?

Any help is greatly appreciated!!

PARAMETERS [Forms]![Form_Main]![GroupName] Text ( 255 );
TRANSFORM Sum(dbo_EHP_OutpatientVisits.PaidAmt) AS SumOfPaidAmt
SELECT Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
FROM (Sub_Group_Information INNER JOIN Group_Information ON
Sub_Group_Information.GroupID = Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Form_Main]![GroupName])) OR
(((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
(([Forms]![Form_Main]![GroupName])="ALL"))
GROUP BY Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
PIVOT dbo_EHP_OutpatientVisits.PaidMonth;
Click to expand...
Click to expand...
 
If the crosstab was for a report, there are some solutions you could find by
googling Hookom Crosstab Report. Otherwise, you could write code to modify
the SQL property of your crosstab query. This could dynamically add the
appropriate column headings in the "PIVOT dbo_EHP_OutpatientVisits.PaidMonth
IN (...)" clause.
--
Duane Hookom
Microsoft Access MVP


shorticake said:
Hi Duane,

Thanks so much for your quick reply. Unfortunately, if I were to force a
column heading I would have to do it nearly 50 times since my data goes back
to 200301, and I would have to add another column each month. Do you have
any other suggestions?

I really appreciate your help, and thanks again.

Duane Hookom said:
You can "force" a column by entering all possible column headings into the
Column Headings property of your crosstab query.

--
Duane Hookom
Microsoft Access MVP


shorticake said:
Whenever I run my query and there is no data for a specific month the
crosstab will skip that month and show the next that contains data (for
example if 200309 has no data then there is no column displayed for 200309).
How can I set it up so that the column heading will still display even if
there is no data for that month?

Any help is greatly appreciated!!

PARAMETERS [Forms]![Form_Main]![GroupName] Text ( 255 );
TRANSFORM Sum(dbo_EHP_OutpatientVisits.PaidAmt) AS SumOfPaidAmt
SELECT Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
FROM (Sub_Group_Information INNER JOIN Group_Information ON
Sub_Group_Information.GroupID = Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Form_Main]![GroupName])) OR
(((dbo_EHP_OutpatientVisits.PaidMonth)>=200309) AND
(([Forms]![Form_Main]![GroupName])="ALL"))
GROUP BY Sub_Group_Information.ReportGroup, Group_Information.BasicOrPrem
PIVOT dbo_EHP_OutpatientVisits.PaidMonth;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Run Query Based on ComboBox Selection 3
Crosstab subcolumn query/report heading pblm 3
column headings in CrossTab Qry? 2
Dynamic Headings in a crosstab query 2
Combo Box for Column Heading 2
Strange Behaviour in Crosstab 4
Crosstab Query Column Headings 2
Using Crosstab query Wizard to sum the quantities in a month. 2

Back
Top