Sorting columns in a Transform/crosstab query

[wmdmurphy]

I'm using the query below to create a crosstab query, and would like to sort
the column headers by project name within a project type. Even though the
underlying query is sorted that way, the resulting transform query still is
showing the columns in straight alphabetical order by project name. Is
there a way to control the sorting?

TRANSFORM Sum(qryProjects.Allocation) AS amount
SELECT qryProjects.Title
FROM qryProjects
GROUP BY qryProjects.Title
PIVOT qryProjects.ProjectName ;

Here's the underlying query, which is named qryProjects:

SELECT tblTransform.Type, tblTransform.ProjectName, tblTransform.Title,
tblTransform.Allocation
FROM tblTransform
ORDER BY tblTransform.Type, tblTransform.ProjectName;

Any help will be appreciated.

Bill

 

[wmdmurphy]

I was afraid that was the case with PIVOT. I've been testing alternative
ways of getting the sort done. After creating the crosstab table I'm trying
to add additional new columns in the correct order using ALTER TABLE, then
populating them with the data from the old columns, then dropping the old
columns. This is working well except with the column names that begin with
numeric characters. The columns are named after real estate projects, some
of which are street addresses like 515 Smith. When I try to add a new
column named 515 Smith I get an Access error "Syntax error in field
definition". If the name does not have leading numeric characters the add
column works. Here's the code:

strFieldName = rstProjectFieldOrder!FieldName

strSQL = "ALTER TABLE tblTransformResults " & _
"ADD COLUMN " & strFieldName & " double;"

db.Execute (strSQL)

Is there a way to format strFieldName in a way that will avoid this error?

Bill

 

[wmdmurphy]

That worked! I'm having to take this approach to accomodate a client's
request.

Thanks.

Bill

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to surround the column (field) name with square brackets [].
E.g.:

"ADD COLUMN [" & strFieldName & "] double;"

I really don't recommend changing the table structure just to get a
correctly formatted report. Perhaps it would be better to reconsider
changing the report's format - not the table's design.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdphBoechKqOuFEgEQI1rQCg1uc2L3D6XcdBXmLQ7YwCdCbvI4AAn2FY
HRYHL3FpnPRuuIJpI4/pD/ut
=cT0d
-----END PGP SIGNATURE-----

I was afraid that was the case with PIVOT. I've been testing alternative
ways of getting the sort done. After creating the crosstab table I'm
trying to add additional new columns in the correct order using ALTER
TABLE, then populating them with the data from the old columns, then
dropping the old columns. This is working well except with the column
names that begin with numeric characters. The columns are named after
real estate projects, some of which are street addresses like 515 Smith.
When I try to add a new column named 515 Smith I get an Access error
"Syntax error in field definition". If the name does not have leading
numeric characters the add column works. Here's the code:

strFieldName = rstProjectFieldOrder!FieldName

strSQL = "ALTER TABLE tblTransformResults " & _
"ADD COLUMN " & strFieldName & " double;"

db.Execute (strSQL)

Is there a way to format strFieldName in a way that will avoid this
error?

Bill

 

[Marshall Barton]

I was afraid that was the case with PIVOT. I've been testing alternative
ways of getting the sort done. After creating the crosstab table I'm trying
to add additional new columns in the correct order using ALTER TABLE, then
populating them with the data from the old columns, then dropping the old
columns. This is working well except with the column names that begin with
numeric characters. The columns are named after real estate projects, some
of which are street addresses like 515 Smith. When I try to add a new
column named 515 Smith I get an Access error "Syntax error in field
definition". If the name does not have leading numeric characters the add
column works. Here's the code:

strFieldName = rstProjectFieldOrder!FieldName

strSQL = "ALTER TABLE tblTransformResults " & _
"ADD COLUMN " & strFieldName & " double;"

db.Execute (strSQL)

Is there a way to format strFieldName in a way that will avoid this error?

I don't think its the number. More likely, it's the space,
try using:

"ADD COLUMN [" & strFieldName & "] double;"

You could get the entries to sort any way you want by having
a table column with a unique numeric sort order and pivoting
on that column. Might be a bit tricky displaying the names
that go with each number though.

 

[wmdmurphy]

You're right, it was the space. I was able to make it work with the change
you suggested.

Thanks.

Bill

I was afraid that was the case with PIVOT. I've been testing alternative
ways of getting the sort done. After creating the crosstab table I'm
trying
to add additional new columns in the correct order using ALTER TABLE, then
populating them with the data from the old columns, then dropping the old
columns. This is working well except with the column names that begin
with
numeric characters. The columns are named after real estate projects,
some
of which are street addresses like 515 Smith. When I try to add a new
column named 515 Smith I get an Access error "Syntax error in field
definition". If the name does not have leading numeric characters the add
column works. Here's the code:

strFieldName = rstProjectFieldOrder!FieldName

strSQL = "ALTER TABLE tblTransformResults " & _
"ADD COLUMN " & strFieldName & " double;"

db.Execute (strSQL)

Is there a way to format strFieldName in a way that will avoid this error?

I don't think its the number. More likely, it's the space,
try using:

"ADD COLUMN [" & strFieldName & "] double;"

You could get the entries to sort any way you want by having
a table column with a unique numeric sort order and pivoting
on that column. Might be a bit tricky displaying the names
that go with each number though.