Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1162
  • Last Modified:

MS Access Function to Sort Columns Alphabetically

This might be tricky.  

I need a function that will sort columns.

For example let's say I have query with the columns

Antelopes    Dogs     Cats
    100            100       100
     50               60         40

I want the function to return the table like this:

Antelopes     Cats       Dogs
     100           100          100
       50             40            60

Lee.


0
lnwright
Asked:
lnwright
  • 2
  • 2
1 Solution
 
lnwrightAuthor Commented:
Oh I forgot to say.   The number of columns will vary.
0
 
tbsgadiCommented:
Hi lnwright,

If you use a crosstab query, Access Sorts the columns alphabetically.

Good Luck!

Gary
0
 
lnwrightAuthor Commented:
Thanks Gary,

That's actually the issue.   I'm already using a crosstab query which indeed does sort alpha but I want to do a bit more manipulation.

Lee.
0
 
tbsgadiCommented:
0
 
harfangCommented:
> ... but I want to do a bit more manipulation.

It would help to know what that is.

As noted above, a pivot without predefined column headers will show them sorted alphabetically, so you have nothing to do.

Further ways to manipulate column order:

a) use or generate column headers

Instead of "PIVOT Species", use "PIVOT Species IN('Antelopes', 'Cats', 'Dogs')". This is also found under the property "column headings" in the property sheet of a pivot table. Note that this is a fixed and complete list of the columns you want. Parrots will not be shown and you will get a column for dogs even if it's entirely Null.

b) write a secondary derived query

    SELECT Antelopes, Cats, Dogs FROM qxtbYourCrosstab

Note that this will break if one of the columns is missing in the crosstab, so it's an inferior solution.

c) manipulate the column order

Open your query, select a column, and move it to a new position. You can always override the SQL column order with the Access column order. This can also be done through VB by manipulating the .ColumnIndex property of fields.

Cheers!
(°v°)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now