Sort by text field in crosstab query

J

JMalecha

Here is a copy of a piece of my table. I need to be able to sort the Monitor
Wells in order (i.e., MW-1B, MW-2, MW-3, MW-12A) Ascending order produces
the results you see here.

Monitor Well 05/06/1999 11/03/1999 05/10/2000
MW-12A 278.36 277.32 278.45
MW-13B 272.83 270.1 272.91
MW-1B 270.96 270.39 271.07
MW-2 274.05 273.87
MW-20 264.97 267.44 264.37
MW-3 269.82 269.3 268.87
 
J

Jerry Whittle

Sorting text is always a problem, not just in a crosstab.

One trick is to pad the data with spaces or zeros. Thus MW-2 becomes MW- 2
or MW-02.

Another trick is to add a SortOrder column to the table with the Monitor
Well data. Manually number the data in a way that you want to see it sorted.
Of course this can be a maintenance nightmare.

If your data always starts with "MW-" and you aren't too worried about
something like MW-13B possibly showing up before MW-13A, there might be a way.

Debug.Print Val(Mid(("MW-13B",4)) = 13

Therefore you could put a field like below in a query and sort on it.

TheSort: Val(Mid([Monitor Well],4))

To get it to work, you may need to do this in a standard select query first,
then base the crosstab on the first query.
 
J

JMalecha

Thank you for your response. I tried replacing the monitor wells like MW-2
with MW-02, but it is still putting MW-20 before MW-15B and MW-14. I do have
to have MW-15A before MW-15B so your second suggestion would not work. I've
tried everything. The crosstab query is running off of a select query, and
the select query is in the proper sort order, but the crosstab is being
difficult. I also tried for the help section adding a column with the Switch
command to give the Monitor Wells a numeric value, but that didn't work
either. Any other suggestions?

Jerry Whittle said:
Sorting text is always a problem, not just in a crosstab.

One trick is to pad the data with spaces or zeros. Thus MW-2 becomes MW- 2
or MW-02.

Another trick is to add a SortOrder column to the table with the Monitor
Well data. Manually number the data in a way that you want to see it sorted.
Of course this can be a maintenance nightmare.

If your data always starts with "MW-" and you aren't too worried about
something like MW-13B possibly showing up before MW-13A, there might be a way.

Debug.Print Val(Mid(("MW-13B",4)) = 13

Therefore you could put a field like below in a query and sort on it.

TheSort: Val(Mid([Monitor Well],4))

To get it to work, you may need to do this in a standard select query first,
then base the crosstab on the first query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JMalecha said:
Here is a copy of a piece of my table. I need to be able to sort the Monitor
Wells in order (i.e., MW-1B, MW-2, MW-3, MW-12A) Ascending order produces
the results you see here.

Monitor Well 05/06/1999 11/03/1999 05/10/2000
MW-12A 278.36 277.32 278.45
MW-13B 272.83 270.1 272.91
MW-1B 270.96 270.39 271.07
MW-2 274.05 273.87
MW-20 264.97 267.44 264.37
MW-3 269.82 269.3 268.87
Click to expand...
Click to expand...
 
J

JMalecha

Okay, I finally figured it out so just in case anyone else is having this
problem, the solution is as follows: Type the following in the firs
available field in design view
Expr1: Switch([Monitor Well]="MW-1B",10,[Monitor Well]="MW-2",11,[Monitor
Well]="MW-3",12,[Monitor Well]="MW-4",13,[Monitor Well]="MW-5",14,[Monitor
Well]="MW-12A",15,[Monitor Well]="MW-13B",16,[Monitor
Well]="MW-14",17,[Monitor Well]="MW-15A",18,[Monitor
Well]="MW-15B",19,[Monitor Well]="MW-18",20,[Monitor
Well]="MW-20",21,[Monitor Well]="MW-CA1",22) Then click Ascending in the Sort
box and (not shown) in the Crosstab box. Note, it is important to start with
10 instead of 1 as Access will order 20 before 15A, etc. if you start with 1.
Hope this helps someone!

JMalecha said:
Thank you for your response. I tried replacing the monitor wells like MW-2
with MW-02, but it is still putting MW-20 before MW-15B and MW-14. I do have
to have MW-15A before MW-15B so your second suggestion would not work. I've
tried everything. The crosstab query is running off of a select query, and
the select query is in the proper sort order, but the crosstab is being
difficult. I also tried for the help section adding a column with the Switch
command to give the Monitor Wells a numeric value, but that didn't work
either. Any other suggestions?

Jerry Whittle said:
Sorting text is always a problem, not just in a crosstab.

One trick is to pad the data with spaces or zeros. Thus MW-2 becomes MW- 2
or MW-02.

Another trick is to add a SortOrder column to the table with the Monitor
Well data. Manually number the data in a way that you want to see it sorted.
Of course this can be a maintenance nightmare.

If your data always starts with "MW-" and you aren't too worried about
something like MW-13B possibly showing up before MW-13A, there might be a way.

Debug.Print Val(Mid(("MW-13B",4)) = 13

Therefore you could put a field like below in a query and sort on it.

TheSort: Val(Mid([Monitor Well],4))

To get it to work, you may need to do this in a standard select query first,
then base the crosstab on the first query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JMalecha said:
Here is a copy of a piece of my table. I need to be able to sort the Monitor
Wells in order (i.e., MW-1B, MW-2, MW-3, MW-12A) Ascending order produces
the results you see here.

Monitor Well 05/06/1999 11/03/1999 05/10/2000
MW-12A 278.36 277.32 278.45
MW-13B 272.83 270.1 272.91
MW-1B 270.96 270.39 271.07
MW-2 274.05 273.87
MW-20 264.97 267.44 264.37
MW-3 269.82 269.3 268.87
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Did you try a calculated field in the query that feed the crosstab like this --
Monitor_Well: Left([Monitor Well], 3) & Right("0000" & Mid([Monitor
Well],4), 5)

JMalecha said:
Thank you for your response. I tried replacing the monitor wells like MW-2
with MW-02, but it is still putting MW-20 before MW-15B and MW-14. I do have
to have MW-15A before MW-15B so your second suggestion would not work. I've
tried everything. The crosstab query is running off of a select query, and
the select query is in the proper sort order, but the crosstab is being
difficult. I also tried for the help section adding a column with the Switch
command to give the Monitor Wells a numeric value, but that didn't work
either. Any other suggestions?

Jerry Whittle said:
Sorting text is always a problem, not just in a crosstab.

One trick is to pad the data with spaces or zeros. Thus MW-2 becomes MW- 2
or MW-02.

Another trick is to add a SortOrder column to the table with the Monitor
Well data. Manually number the data in a way that you want to see it sorted.
Of course this can be a maintenance nightmare.

If your data always starts with "MW-" and you aren't too worried about
something like MW-13B possibly showing up before MW-13A, there might be a way.

Debug.Print Val(Mid(("MW-13B",4)) = 13

Therefore you could put a field like below in a query and sort on it.

TheSort: Val(Mid([Monitor Well],4))

To get it to work, you may need to do this in a standard select query first,
then base the crosstab on the first query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JMalecha said:
Here is a copy of a piece of my table. I need to be able to sort the Monitor
Wells in order (i.e., MW-1B, MW-2, MW-3, MW-12A) Ascending order produces
the results you see here.

Monitor Well 05/06/1999 11/03/1999 05/10/2000
MW-12A 278.36 277.32 278.45
MW-13B 272.83 270.1 272.91
MW-1B 270.96 270.39 271.07
MW-2 274.05 273.87
MW-20 264.97 267.44 264.37
MW-3 269.82 269.3 268.87
Click to expand...
Click to expand...
Click to expand...
 
Top