Crosstab Row Sorting in Alphabetical Order

  • Thread starter Thread starter alison.justice
  • Start date Start date
A

alison.justice

I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
 
It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])
 
Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


Duane Hookom said:
It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
 
Put it where "I have the rank (count) in the report under the grouping section"

I assume this is the sorting and grouping dialog in report design.

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


Duane Hookom said:
It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
It didn't work, do I need to replace the control source with it, or just put
it in the grouping and sort dialog box?
--
Alison


Duane Hookom said:
Put it where "I have the rank (count) in the report under the grouping section"

I assume this is the sorting and grouping dialog in report design.

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


Duane Hookom said:
It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


:

I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
What exactly do you have in the sorting and grouping dialog?
Is the Rank field left or right aligned in the datasheet view?

Are there any lookup fields in your tables?

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
It didn't work, do I need to replace the control source with it, or just put
it in the grouping and sort dialog box?
--
Alison


Duane Hookom said:
Put it where "I have the rank (count) in the report under the grouping section"

I assume this is the sorting and grouping dialog in report design.

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


:

It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


:

I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
I have the following three items in the dialog box before the rank that are
grouped with a header, the rank does not have a header It reads like:

PrimaryID Ascending (Grouped with Header)
Category Description Ascending (Grouped with a Header)
Category Detail Ascending (Grouped with a Header)
Rank Ascending (No Header)

The Rank field shows to be left aligned. None of the tables have a look up
field
--
Alison


Duane Hookom said:
What exactly do you have in the sorting and grouping dialog?
Is the Rank field left or right aligned in the datasheet view?

Are there any lookup fields in your tables?

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
It didn't work, do I need to replace the control source with it, or just put
it in the grouping and sort dialog box?
--
Alison


Duane Hookom said:
Put it where "I have the rank (count) in the report under the grouping section"

I assume this is the sorting and grouping dialog in report design.

--
Duane Hookom
Microsoft Access MVP


:

Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


:

It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


:

I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
Did you try as I suggested by changing the sorting and grouping expression to:
=Val([Rank])

BTW: your example seemed to be sorted correctly. Why do you need to change it?
--
Duane Hookom
Microsoft Access MVP


alison.justice said:
I have the following three items in the dialog box before the rank that are
grouped with a header, the rank does not have a header It reads like:

PrimaryID Ascending (Grouped with Header)
Category Description Ascending (Grouped with a Header)
Category Detail Ascending (Grouped with a Header)
Rank Ascending (No Header)

The Rank field shows to be left aligned. None of the tables have a look up
field
--
Alison


Duane Hookom said:
What exactly do you have in the sorting and grouping dialog?
Is the Rank field left or right aligned in the datasheet view?

Are there any lookup fields in your tables?

--
Duane Hookom
Microsoft Access MVP


alison.justice said:
It didn't work, do I need to replace the control source with it, or just put
it in the grouping and sort dialog box?
--
Alison


:

Put it where "I have the rank (count) in the report under the grouping section"

I assume this is the sorting and grouping dialog in report design.

--
Duane Hookom
Microsoft Access MVP


:

Yes they are numbers and it starts at 1 example of what it looks like:

Rank Column1 Column2
1 WED5700 White WGD6400 White
2 WED6400 White

Where exactly would I put the =Val([Rank]) in the report?

Thank you so much for your help.....
--
Alison


:

It would help if you provided some values from the Rank field in the order
you are seeing them in the report.

If the Rank field seems like numbers but is left-aligned in datasheet view,
it is being evaluated as text/string. You might be able to simply set the
sorting and grouping expression to:
=Val([Rank])

--
Duane Hookom
Microsoft Access MVP


:

I have a report based off of a crosstab query that the data under the columns
is text. I am trying to sort them in alphabetical order and keep them lined
up. I have the rank (count) in the report under the grouping section to sort
in ascending order but it sorts the text backwards, when I change it to
decending it will sort the longest column in order but the smaller columns it
puts at the bottom of the column instead of starting at the top.

Following is the code for the Crosstab:

SELECT Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6
FROM Table_Customer INNER JOIN Query_Customer_Catalog_Crosstab_Final ON
(Table_Customer.NeighborhoodID =
Query_Customer_Catalog_Crosstab_Final.neighborhoodid) AND
(Table_Customer.BuilderID = Query_Customer_Catalog_Crosstab_Final.builderid)
GROUP BY Table_Customer.PrimaryID,
Query_Customer_Catalog_Crosstab_Final.builderid,
Query_Customer_Catalog_Crosstab_Final.neighborhoodid,
Query_Customer_Catalog_Crosstab_Final.categoryid,
Query_Customer_Catalog_Crosstab_Final.categorydescription,
Query_Customer_Catalog_Crosstab_Final.categorydetaildescription,
Query_Customer_Catalog_Crosstab_Final.rank,
Query_Customer_Catalog_Crosstab_Final.S,
Query_Customer_Catalog_Crosstab_Final.U2,
Query_Customer_Catalog_Crosstab_Final.U3,
Query_Customer_Catalog_Crosstab_Final.U4,
Query_Customer_Catalog_Crosstab_Final.U5,
Query_Customer_Catalog_Crosstab_Final.U6;

I'm not an advanced user so you might have to step me through what I would
need to do.

Any ideas or suggestions that you could give is really appreciated. I am
using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top