Unusual Sorting Requirement

[croy]

The boss wants to see a "table" with a difficult sorting
pattern.

Where [Field1] has only two possible values: "WD" or "WE",
and [Field2] has about 25 possible values, like 1.1, 1.2,
2.1, 3, etc., [Field3] is a date field, and [Field4] has
numeric values like 0.119749563.

Boss wants to see column headings like:

WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc.

Row Heading of the date field ([Field3])

and [Field4] in the field.

The column heading, combining [Field1] and [Field2], where
it alternates for the value in [Field1], has me completely
stumped.

Any thoughts appreciated.

 

[Golfinray]

On your form, put labels next to the fields you want to sort. Like for your
1.1, put a label next to it with another label next to it that says sort by
1.1 Then in the events of the first label, on the onclick event type:
Me.Orderby = "[1.1]"
Me.Orderbyon = true
Do this on each item you want to sort by. All you will have is a label stuck
beside each field with this code and a little label above it saying sort by
this. On the table itself, this will be very hard to automate. Use a form
with all that data there and it is easy.

 

[John Spencer]

Looks like a crosstab query.

TRANSFORM First(Field4) as theValue
SELECT Field3
FROM YourTable
GROUP BY Field3
PIVOT Field1 & Field2

The problem is that this will put all the WD first and then all the WE.
If you can use

PIVOT Field2 & Field1

you might come closer to the solution.

IF you know all the combinations you can use an In Clause to set the
column order

Pivot Field1 & Field2 in ("WD1.1", "WE1.1",....,"WD19.1","WE19.1")

The ONLY columns that will show up are those listed in the IN clause and
those in the Group By clause.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

I do not follow what is to be under the column labels.
Work QTY WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc
1/2/08 2.34 ? ? ? ? ?
2/5/08 5.32 ? ? ? ? ?
What data goes where the question marks are?

 

[croy]

I do not follow what is to be under the column labels.
Work QTY WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc
1/2/08 2.34 ? ? ? ? ?
2/5/08 5.32 ? ? ? ? ?
What data goes where the question marks are?

Sorry Karl... and thanks for the reply!

Here's what it should look like:

Date WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc.
1/2/08 2.3457 1.1533 1.9793 0.9321 1.8858 ...
2/5/08 5.3244 2.1867 2.1183 1.8749 0.4437 ...

 

[croy]

Thanks to Golfinray, John Spencer, and Karl Dewey for their
excellent replies.

More clarification, and an interesting work-around, below...

The boss wants to see a "table" with a difficult sorting
pattern.

Where [Field1] has only two possible values: "WD" or "WE",
and [Field2] has about 25 possible values, like 1.1, 1.2,
2.1, 3, etc., [Field3] is a date field, and [Field4] has
numeric values like 0.119749563.

Boss wants to see column headings like:

WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc.

Row Heading of the date field ([Field3])

and [Field4] in the field.

The column heading, combining [Field1] and [Field2], where
it alternates for the value in [Field1], has me completely
stumped.

More clarity. Here's the desired layout:

Date WD1.1 WE1.1 WD1.2 WE1.2 WD2.1 etc.
1/2/08 2.3457 1.1533 1.9793 0.9321 1.8858 ...
2/5/08 5.3244 2.1867 2.1183 1.8749 0.4437 ...

Interesting work-around:

In the datasheet view of this query, I manually hauled the
columns into the order desired, then saved and closed the
query. Upon re-opening the query, the column-order that I
had arranged had been preserved! Just about fell off my
chair. I have yet to figure out where and how that was
saved in the query, but I guess if it's doing what is
desired, I shouldn't care that much. Of course, some time
in the future, I'll probably not want that behaviour for
some reason, and I won't know how to stop it!

Thanks again for all who took the time to reply. I am
humbled by the generosity I find in these Access forums.