Re: Sorting columns in a Transform/crosstab query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Thanks.

Bill

"MGFoster" <me@xxxxxxxxxxx> wrote in message
news:tJSdnbjno7u5_EfUnZ2dnUVZ_jyWnZ2d@xxxxxxxxxxxxxxxx
-----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-----


wmdmurphy wrote:
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



"MGFoster" <me@xxxxxxxxxxx> wrote in message
news:N7CdnTI1JIUotkfUnZ2dnUVZ_sudnZ2d@xxxxxxxxxxxxxxxx
wmdmurphy wrote:
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.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't sort the PIVOT values and it doesn't matter if the source is
sorted. The PIVOT always sorts its values alphabetically/numerically.
The only way you can get the PIVOT values to be in the order you want is
to explicitly state their order. E.g.:

PIVOT ProjectName ("SomeOtherProject", "Gardening", "Landscaping")

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/AwUBSdorw4echKqOuFEgEQIDhQCdH2MHAKpatmrM6eDuNNNa0Z3z8j4AoLpb
LT4/Nrl4/ITrvPblY0D8IUcp
=yPTy
-----END PGP SIGNATURE-----





.

Relevant Pages