In a database I maintain  that is used to keep racquetball tournament 

results and current standings, I am creating a report that shows current 

standings for each division.  The report is grouped first by Division Type 

(Singles or Doubles), and then Division Name.  Each division is stored in 

tblDivisions with fields for Division, DivisionName, DivisionType, and 

DivisionGender (Men, Women, or Mixed).  The Division field could probbly 

be named better as DivisionCode, because it is a 2 to 4 character code.  

For instance, Men's Open is MO, Women's A is WA, Mixed Open Doubles is 

XOD, etc.  tblScores contains the results for each person for one 

division.  The fields are Division, FullName, TournamentID, Points, and 

TournamentYear.



In the final report, I want to show the total each person in a division has

(most points to least), but I also want them to be able to see how many

points the person has earned for each tournament, so I based the report on 

a crosstab query.  To start, I created a regular query called 

qryAllScoreInfo.  The SQL is:



SELECT tblScores.Division, tblDivisions.DivisionType,

tblDivisions.DivisionName, tblScores.FullName, tblScores.TournamentID,

tblTournaments.Name, tblScores.Points

FROM tblTournaments RIGHT JOIN (tblDivisions INNER JOIN tblScores ON

tblDivisions.Division = tblScores.Division) ON tblTournaments.TournamentID 

=tblScores.TournamentID;



I then made a crosstab query called qryAllScoreInfo_Crosstab that looks 

like this:



TRANSFORM Sum([qryAllScoreInfo].[Points]) AS SumOfPoints

SELECT [qryAllScoreInfo].[DivisionType], [qryAllScoreInfo].[DivisionName],

[qryAllScoreInfo].[FullName], Sum([qryAllScoreInfo].[Points]) AS [Total Of

Points]

FROM qryAllScoreInfo

GROUP BY [qryAllScoreInfo].[DivisionType], [qryAllScoreInfo].

[DivisionName], [qryAllScoreInfo].[FullName]

ORDER BY [qryAllScoreInfo].[DivisionType] DESC

PIVOT [qryAllScoreInfo].[TournamentID];



and based the report on this query.



The point of all this is that I would like to arbitarily sort the order 

that the divisions are listed in.  The division codes that are used to 

sort the singles divisions are



M30+

M35A

M35B

M45A

M45B

M55+

M60+

MA

MB

MC

MD

MO

WO

WA

WB

WC

WD



but I would like to sort them in this order:



MO

MA

MB

MC

MD

M30+

M35A

M35B

M45A

M45B

M55+

M60+

WO

WA

WB

WC

WD



I did some research on Google, and one way I saw suggested was to add

another field  to tblDivisions called SortOrder, and assign each division a

numerical search order.  The problem is, a crosstab query only allows three

row heading columns, and I'm already using all three.  It would seem to me

that it could be done easily in VBA, but I'm not sure how.   Can someone

give me an idea of which event to use and what code  I could use?  I'm

thinking I could use something like a Select Case statement, but I'm not

sure.



Thanks.



Steve

I think you can just add the SortOrder field to tblDivisions and then create

a new query, joining tblDivisions with your crosstab query & sort in that

new query.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Steve Edwards [mailto:killshot@w...]

Sent: Friday, November 09, 2001 7:08 AM

To: Access

Subject: [access] Arbitrary Sorting Of Records In Report





In a database I maintain  that is used to keep racquetball tournament 

results and current standings, I am creating a report that shows current 

standings for each division.  The report is grouped first by Division Type 

(Singles or Doubles), and then Division Name.  Each division is stored in 

tblDivisions with fields for Division, DivisionName, DivisionType, and 

DivisionGender (Men, Women, or Mixed).  The Division field could probbly 

be named better as DivisionCode, because it is a 2 to 4 character code.  

For instance, Men's Open is MO, Women's A is WA, Mixed Open Doubles is 

XOD, etc.  tblScores contains the results for each person for one 

division.  The fields are Division, FullName, TournamentID, Points, and 

TournamentYear.



In the final report, I want to show the total each person in a division has

(most points to least), but I also want them to be able to see how many

points the person has earned for each tournament, so I based the report on 

a crosstab query.  To start, I created a regular query called 

qryAllScoreInfo.  The SQL is:



SELECT tblScores.Division, tblDivisions.DivisionType,

tblDivisions.DivisionName, tblScores.FullName, tblScores.TournamentID,

tblTournaments.Name, tblScores.Points

FROM tblTournaments RIGHT JOIN (tblDivisions INNER JOIN tblScores ON

tblDivisions.Division = tblScores.Division) ON tblTournaments.TournamentID 

=tblScores.TournamentID;



I then made a crosstab query called qryAllScoreInfo_Crosstab that looks 

like this:



TRANSFORM Sum([qryAllScoreInfo].[Points]) AS SumOfPoints

SELECT [qryAllScoreInfo].[DivisionType], [qryAllScoreInfo].[DivisionName],

[qryAllScoreInfo].[FullName], Sum([qryAllScoreInfo].[Points]) AS [Total Of

Points]

FROM qryAllScoreInfo

GROUP BY [qryAllScoreInfo].[DivisionType], [qryAllScoreInfo].

[DivisionName], [qryAllScoreInfo].[FullName]

ORDER BY [qryAllScoreInfo].[DivisionType] DESC

PIVOT [qryAllScoreInfo].[TournamentID];



and based the report on this query.



The point of all this is that I would like to arbitarily sort the order 

that the divisions are listed in.  The division codes that are used to 

sort the singles divisions are



M30+

M35A

M35B

M45A

M45B

M55+

M60+

MA

MB

MC

MD

MO

WO

WA

WB

WC

WD



but I would like to sort them in this order:



MO

MA

MB

MC

MD

M30+

M35A

M35B

M45A

M45B

M55+

M60+

WO

WA

WB

WC

WD



I did some research on Google, and one way I saw suggested was to add

another field  to tblDivisions called SortOrder, and assign each division a

numerical search order.  The problem is, a crosstab query only allows three

row heading columns, and I'm already using all three.  It would seem to me

that it could be done easily in VBA, but I'm not sure how.   Can someone

give me an idea of which event to use and what code  I could use?  I'm

thinking I could use something like a Select Case statement, but I'm not

sure.



Thanks.



Steve

> I think you can just add the SortOrder field to tblDivisions and then 

create

> a new query, joining tblDivisions with your crosstab query & sort in that

> new query.

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

> 



This is a combination of tw posts to another ng.



Okay, I'm trying something, and I think I'm getting close.  I added a

number field called SortOrder to tblDivisions, and used that to specifiy 

the arbitrary order I want to use.  I then added it to the original query, 

so it now looks like this:



 SELECT tblScores.Division, tblDivisions.DivisionType,

 tblDivisions.DivisionName, tblScores.FullName, tblScores.TournamentID,

 tblTournaments.Name, tblScores.Points, tblDivisions.SortOrder

 FROM tblTournaments RIGHT JOIN (tblDivisions INNER JOIN tblScores ON

 tblDivisions.Division = tblScores.Division) ON tblTournaments.TournamentID

= tblScores.TournamentID

 ORDER BY tblDivisions.DivisionType DESC , tblDivisions.SortOrder;



 and the resulting crosstab query looks  like  this:



 TRANSFORM Sum(qryAllScoreInfo_Sort.Points) AS SumOfPoints

 SELECT qryAllScoreInfo_Sort.DivisionType, qryAllScoreInfo_Sort.SortOrder,

 qryAllScoreInfo_Sort.FullName, Sum(qryAllScoreInfo_Sort.Points) AS [Total

Of Points]

 FROM qryAllScoreInfo_Sort

 GROUP BY qryAllScoreInfo_Sort.DivisionType,qryAllScoreInfo_Sort.SortOrder,

 qryAllScoreInfo_Sort.FullName

 ORDER BY qryAllScoreInfo_Sort.SortOrder

 PIVOT qryAllScoreInfo_Sort.TournamentID;



 In the report, I have three grouping levels: DivisionType, SortOrder, and

 FullName.  Everything looks like I want it to in the report, except  for

the division name.  I need to replace the SortOrder value with the

corresponding division name.  For starters I tried this:

>

 Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)



 Dim strDivision As String



 If SortOrder = 1 Then

     strDivision = "Men's Open"

     Me!txtDivisionType = strDivision

 End If



 End Sub



with the idea that if that worked, I would use a Select Case statement to

specifiy all the division names.  However, when I run the report, I get an

run time error 2448 that says "You can't assign a value to this object."

Is there another way I can show a different value on the report instead of

the number from the sort order field?  Or do I need to try another approach

like basing the report on a recordset?







I then tried something a little different.  I modified my SortOrder field 

so that it also contains the division name.  For instance, if DivisionName 

is "Men's Open", then the SortOrder field is "1 - Men's Open".  What I 

want to do is display what comes after the dash while still displaying the 

number, so I tried with an expression like this:



=right(SortOrder, len(SortOrder)-InStr(0,[SortOrder],"-",0))



but all that shows up is "#Error" in the field on the report.  I have a

feeling I need to dim a string variable and place whatever is in SortOrder

into that, but I'm not sure how to do that with an expression.  Do I need 

to do it in the ObFormat event inVBA instead?  If so, how do I get the 

value into the field, since what I tried above didn't work?



My other question is, how do I get Access to sort on the numbers properly?

There are 27 divisions, but instead of sorting 1,2,3,4,5, etc, it goes

1,10,11,12,13, etc.  How do I get them to sort in the proper order?



Thanks.



Steve

Are you up for quick 'n dirty? ... Since your division code is kind of 

cryptic anyways, I assume you're depending on the description of that code 

to tell the tale. So, change your division code to prefix a 2-digit 

numeric character to establish your sort order. Do you really care if a 

code is M35 or 22M35?

With your 27 divisions not sorting up, that's because they're left-

justified characters. Either make the field numeric, or right-justify the 

value into the field, and it'll sort fine.

... AL







> 

> Okay, I'm trying something, and I think I'm getting close.  I added a

> number field called SortOrder to tblDivisions, and used that to specifiy 

> the arbitrary order I want to use.  I then added it to the original 

query, 



 . . .

> 

> My other question is, how do I get Access to sort on the numbers 

properly?

> There are 27 divisions, but instead of sorting 1,2,3,4,5, etc, it goes

> 1,10,11,12,13, etc.  How do I get them to sort in the proper order?

> 

> Thanks.

> 

> Steve

> 

> Are you up for quick 'n dirty? ... Since your division code is kind of 

> cryptic anyways, I assume you're depending on the description of that 

code 

> to tell the tale. So, change your division code to prefix a 2-digit 

> numeric character to establish your sort order. Do you really care if a 

> code is M35 or 22M35?

> With your 27 divisions not sorting up, that's because they're left-

> justified characters. Either make the field numeric, or right-justify 

the 

> value into the field, and it'll sort fine.

> ... AL



I really like your idea, but I already have a ton of entries in tblScores, 

and the Division(Code) field is the one that links the records in 

tblScores to the division names in tblDivisions, so it wouldn't be too 

quick.  I changed the prefixes that I have in the SortOrder field for the 

first nine divisions to two digit numbers (1 to 01, 2 to 02, etc.), and 

everything sorts properly.  



What I ended up doing to get the divisions sorted properly in the final 

report in addition to the change I mentioned above was to get rid of the 

dash and spaces in the DivisionName field (so it is 01Men's Open, 02 Men's 

A, etc.), and then make the expression in the Sort Order heading on the 

report



=Right(SortOrder, Len(SortOrder)-2)



Thanks for the help, Alan.  That got me going in the right direction.



Steve

You can sort on a report field without displaying it--just set the group

header and group footer properties to "No".  I think if you sort on both

SortOrder (not displayed) and Division (displayed) you should get what you

need.



As for the odd sorting, I'm guessing you've made SortOrder a text field?  If

you make it a numeric it should sort as you intend.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Steve Edwards [mailto:killshot@w...]

Sent: Saturday, November 10, 2001 5:25 PM

To: Access

Subject: [access] RE: Arbitrary Sorting Of Records In Report





> I think you can just add the SortOrder field to tblDivisions and then 

create

> a new query, joining tblDivisions with your crosstab query & sort in that

> new query.

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

> 



This is a combination of tw posts to another ng.



Okay, I'm trying something, and I think I'm getting close.  I added a

number field called SortOrder to tblDivisions, and used that to specifiy 

the arbitrary order I want to use.  I then added it to the original query, 

so it now looks like this:



 SELECT tblScores.Division, tblDivisions.DivisionType,

 tblDivisions.DivisionName, tblScores.FullName, tblScores.TournamentID,

 tblTournaments.Name, tblScores.Points, tblDivisions.SortOrder

 FROM tblTournaments RIGHT JOIN (tblDivisions INNER JOIN tblScores ON

 tblDivisions.Division = tblScores.Division) ON tblTournaments.TournamentID

= tblScores.TournamentID

 ORDER BY tblDivisions.DivisionType DESC , tblDivisions.SortOrder;



 and the resulting crosstab query looks  like  this:



 TRANSFORM Sum(qryAllScoreInfo_Sort.Points) AS SumOfPoints

 SELECT qryAllScoreInfo_Sort.DivisionType, qryAllScoreInfo_Sort.SortOrder,

 qryAllScoreInfo_Sort.FullName, Sum(qryAllScoreInfo_Sort.Points) AS [Total

Of Points]

 FROM qryAllScoreInfo_Sort

 GROUP BY qryAllScoreInfo_Sort.DivisionType,qryAllScoreInfo_Sort.SortOrder,

 qryAllScoreInfo_Sort.FullName

 ORDER BY qryAllScoreInfo_Sort.SortOrder

 PIVOT qryAllScoreInfo_Sort.TournamentID;



 In the report, I have three grouping levels: DivisionType, SortOrder, and

 FullName.  Everything looks like I want it to in the report, except  for

the division name.  I need to replace the SortOrder value with the

corresponding division name.  For starters I tried this:

>

 Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)



 Dim strDivision As String



 If SortOrder = 1 Then

     strDivision = "Men's Open"

     Me!txtDivisionType = strDivision

 End If



 End Sub



with the idea that if that worked, I would use a Select Case statement to

specifiy all the division names.  However, when I run the report, I get an

run time error 2448 that says "You can't assign a value to this object."

Is there another way I can show a different value on the report instead of

the number from the sort order field?  Or do I need to try another approach

like basing the report on a recordset?







I then tried something a little different.  I modified my SortOrder field 

so that it also contains the division name.  For instance, if DivisionName 

is "Men's Open", then the SortOrder field is "1 - Men's Open".  What I 

want to do is display what comes after the dash while still displaying the 

number, so I tried with an expression like this:



=right(SortOrder, len(SortOrder)-InStr(0,[SortOrder],"-",0))



but all that shows up is "#Error" in the field on the report.  I have a

feeling I need to dim a string variable and place whatever is in SortOrder

into that, but I'm not sure how to do that with an expression.  Do I need 

to do it in the ObFormat event inVBA instead?  If so, how do I get the 

value into the field, since what I tried above didn't work?



My other question is, how do I get Access to sort on the numbers properly?

There are 27 divisions, but instead of sorting 1,2,3,4,5, etc, it goes

1,10,11,12,13, etc.  How do I get them to sort in the proper order?



Thanks.



Steve