Column Headings in Crosstab Query

[Guest]

Using Access 2000

Is it possible to change column headings ? I have a crosstab query that
counts the number of records for a particular primary diagnosis (this field
has the following parameters set in design view Row Source Type Value List,
Row Source "C48";"textstring1";"C49";"textstring2"..., Bound Column 1. The
Count headings on the Crosstab show
"Total of NHS Number" - can this be changed to "Total"
C48 - can this be changed to "textstring1"
C49 - can this be changed to "textstring2" etc

SQL code
TRANSFORM Count(Demographics.[NHS Number]) AS [CountOfNHS Number]
SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total Of
NHS Number]
FROM Demographics
GROUP BY Demographics.Hospital
PIVOT Demographics.[Primary diagnosis];

 

[Jamie Richards]

Hi,

Try changing this line;

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total Of
NHS Number]

to this:

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS Total

Based on what I think you're saying, it is the alias name applied to the
aggregated [NHS Number] in your select statement that needs to be changed.

I probably won't help much here as I'm not sure what you're trying to do
but, do you have any records for textstring1 and textstring2? If so, can
you just remove the names in the rowsource and replace them with the ones
you want?

HTH

Jamie

 

[Guest]

Thanks for answer for first part - the other column headers C48, C49 etc -
these are the values stored in the demographics table in the field [primary
diagnosis]. this field has been set up so that a user is presented with a
text string rather than a code (C48, C49...) - this was done by means of a
value list with the list being defined as
"C48";"description1";"C49";"description2"; and so on with the bound column
being the C48,C49 code not the description. this means that the CrossTab
outputs the bound column value C48, C49, etc, and not the "description" - is
it possible to amend the query so that the column headers report the
'unbound' column

Hi,

Try changing this line;

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total Of
NHS Number]

to this:

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS Total

Based on what I think you're saying, it is the alias name applied to the
aggregated [NHS Number] in your select statement that needs to be changed.

I probably won't help much here as I'm not sure what you're trying to do
but, do you have any records for textstring1 and textstring2? If so, can
you just remove the names in the rowsource and replace them with the ones
you want?

HTH

Jamie

Using Access 2000

Is it possible to change column headings ? I have a crosstab query that
counts the number of records for a particular primary diagnosis (this
field
has the following parameters set in design view Row Source Type Value
List,
Row Source "C48";"textstring1";"C49";"textstring2"..., Bound Column 1. The
Count headings on the Crosstab show
"Total of NHS Number" - can this be changed to "Total"
C48 - can this be changed to "textstring1"
C49 - can this be changed to "textstring2" etc

SQL code
TRANSFORM Count(Demographics.[NHS Number]) AS [CountOfNHS Number]
SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total
Of
NHS Number]
FROM Demographics
GROUP BY Demographics.Hospital
PIVOT Demographics.[Primary diagnosis];

 

[Jamie Richards]

Hmm...You might need someone smarter than me to help you with this one. ;o)

I'm still not sure what you're tryig to do, but it looks a bit like you have
semi-colon separated values in a rowsource for a listbox or combobox lookup
in a table field, is that right? If so, any values you enter in that
rowsource will become values available for selection. You can't put column
names in there as well (whilst using it as a value list). You would name
the field in the Field Name section of the table in design view, then enter
your value list as you have suggested.

Is the C48, C49, etc supposed to be a code for an item which would be named,
for example, "Description1", "Description2", etc? If so, have you
considered using a separate table to represent whatever those items are?
Say if it were cars you could have:
______________
CarID | Make
--------|---------
1 | Ford
2 | Chevvy
3 | Lincoln
------------------

Then join to that table in your query. It seems like you have tried to
specify what amounts to a table/query lookup using the Value List
settings...that no worky as far as I know.

Jamie

Thanks for answer for first part - the other column headers C48, C49 etc -
these are the values stored in the demographics table in the field
[primary
diagnosis]. this field has been set up so that a user is presented with a
text string rather than a code (C48, C49...) - this was done by means of a
value list with the list being defined as
"C48";"description1";"C49";"description2"; and so on with the bound column
being the C48,C49 code not the description. this means that the CrossTab
outputs the bound column value C48, C49, etc, and not the "description" -
is
it possible to amend the query so that the column headers report the
'unbound' column

Hi,

Try changing this line;

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total
Of
NHS Number]

to this:

SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS Total

Based on what I think you're saying, it is the alias name applied to the
aggregated [NHS Number] in your select statement that needs to be
changed.

I probably won't help much here as I'm not sure what you're trying to do
but, do you have any records for textstring1 and textstring2? If so, can
you just remove the names in the rowsource and replace them with the ones
you want?

HTH

Jamie

Using Access 2000

Is it possible to change column headings ? I have a crosstab query that
counts the number of records for a particular primary diagnosis (this
field
has the following parameters set in design view Row Source Type Value
List,
Row Source "C48";"textstring1";"C49";"textstring2"..., Bound Column 1.
The
Count headings on the Crosstab show
"Total of NHS Number" - can this be changed to "Total"
C48 - can this be changed to "textstring1"
C49 - can this be changed to "textstring2" etc

SQL code
TRANSFORM Count(Demographics.[NHS Number]) AS [CountOfNHS Number]
SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS
[Total
Of
NHS Number]
FROM Demographics
GROUP BY Demographics.Hospital
PIVOT Demographics.[Primary diagnosis];