Re: Sorting columns in a Transform/crosstab query

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

-----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

  • Re: Sorting columns in a Transform/crosstab query
    ... Hash: SHA1 ... sort the column headers by project name within a project type. ... You can't sort the PIVOT values and it doesn't matter if the source is ...
    (microsoft.public.access.queries)
  • Re: In which cases/problems is Prolog faster than Java?
    ... Carl Hoare's 1960's quick sort is starting to wear thin in the year 2008 ... So what about parallel implementations of qsort for Prolog? ... partition(Rest, Pivot, Smaller0, Bigger0), ... qsort(Bigger0, Acc, Bigger, Threads2) ...
    (comp.lang.prolog)
  • Re: sorting columns
    ... I wanted to make sure i was not missing some internal functions to help me ... Pivot tables are more like ... If you plan on creating your custom sort code, ... First, if you ever had a course on programming dealing with sorting, ...
    (microsoft.public.excel.programming)
  • Re: Pivot Table sort by count of crashes 2008 but not 2004
    ... I then perform a pivot table report and do a sort on one of the columns. ... descending on the count of that column, Excel 08 always crashes. ...
    (microsoft.public.mac.office.excel)
  • Re: RELENG_7_0: vm_thread_new: kstack allocation failed
    ... Hash: SHA1 ... It seems that I have hit some sort of "leak" or some exhaustion issue. ... FreeBSD - The Power to Serve! ...
    (freebsd-current)