Sorting A Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello I have a crosstab query that I would like have sorted based on criteria
from another field. The Row Heading is from a table called Name and the field
is NAME. The Column Heading Is NickName taken from a table called Schedule.
The Value is from another field called Driver from a table called Picks. It
looks graet. I just would like to sort the cloumn heading with a field called
RACENO ascendng. This field is in both the Schedule and Picks Table. Any
SUggestions.
Thanks

Chris
 
Have you tried adding RACENO to the query grid and sorting by it? Have you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table, field,
variable,...) Every object has a Name property which can be very confusing.
Try create a new blank report based on your Name table and add a text box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you see in
the text box?
 
I tried putting RaceNo in the Criteria of the columnrow as ascending. It did
not work. I even tried making a fourth column and used WHERE and EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't sure what I
was doing. Any help would be great.

Chris

Duane Hookom said:
Have you tried adding RACENO to the query grid and sorting by it? Have you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table, field,
variable,...) Every object has a Name property which can be very confusing.
Try create a new blank report based on your Name table and add a text box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you see in
the text box?

--
Duane Hookom
MS Access MVP
--

Diggsy said:
Hello I have a crosstab query that I would like have sorted based on
criteria
from another field. The Row Heading is from a table called Name and the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called Picks.
It
looks graet. I just would like to sort the cloumn heading with a field
called
RACENO ascendng. This field is in both the Schedule and Picks Table. Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
 
I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

Diggsy said:
I tried putting RaceNo in the Criteria of the columnrow as ascending. It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't sure what
I
was doing. Any help would be great.

Chris

Duane Hookom said:
Have you tried adding RACENO to the query grid and sorting by it? Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a text box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Diggsy said:
Hello I have a crosstab query that I would like have sorted based on
criteria
from another field. The Row Heading is from a table called Name and the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a field
called
RACENO ascendng. This field is in both the Schedule and Picks Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




Duane Hookom said:
I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

Diggsy said:
I tried putting RaceNo in the Criteria of the columnrow as ascending. It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't sure what
I
was doing. Any help would be great.

Chris

Duane Hookom said:
Have you tried adding RACENO to the query grid and sorting by it? Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a text box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted based on
criteria
from another field. The Row Heading is from a table called Name and the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a field
called
RACENO ascendng. This field is in both the Schedule and Picks Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
What happens if you try:
TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.RaceNo, Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.RaceNo, Picks.Name
PIVOT Schedule.NickName;
--
Duane Hookom
MS Access MVP


Diggsy said:
My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




Duane Hookom said:
I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

Diggsy said:
I tried putting RaceNo in the Criteria of the columnrow as ascending. It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't sure
what
I
was doing. Any help would be great.

Chris

:

Have you tried adding RACENO to the query grid and sorting by it? Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a text
box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you
see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted based on
criteria
from another field. The Row Heading is from a table called Name and
the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a
field
called
RACENO ascendng. This field is in both the Schedule and Picks Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
It looks like this. Not what I need
RaceNo Name Atlanta Daytona Rock Vegas
1 CHRIS Jamie McMurray
1 JIM Bobby Labonte
1 JORDAN Rusty Wallace
1 STEVE Dale Earnhardt Jr.
1 TOM Elliott Sadler
2 CHRIS Jamie McMurray
2 JIM Greg Biffle
2 JORDAN Jamie McMurray
2 STEVE Rusty Wallace
2 TOM Dale Jarrett
3 CHRIS Dale Jarrett
3 JIM Greg Biffle
3 JORDAN Bobby Labonte
3 STEVE Kurt Busch
3 TOM Ryan Newman
4 CHRIS Bobby Labonte
4 JIM Elliott Sadler
4 JORDAN Kevin Harvick
4 STEVE Bobby Labonte
4 TOM Jimmie Johnson

When I use RaceNo in the Columnhead it looks like this

Name 1 2 3
CHRIS Jamie McMurray Jamie McMurray Dale Jarrett
JIM Bobby Labonte Greg Biffle Greg Biffle
JORDAN Rusty Wallace Jamie McMurray Bobby Labonte
STEVE Dale Earnhardt Jr. Rusty Wallace Kurt Busch
TOM Elliott Sadler Dale Jarrett Ryan Newman

The Races Go Daytona Rock Vegas
When I use the Nickname It looks like this

Name LasVegas Daytona Rock
CHRIS Dale Jarrett Jamie McMurray Jamie McMurray
JIM Greg Biffle Bobby Labonte Greg Biffle
JORDAN Bobby Labonte Rusty Wallace Jamie McMurray
STEVE Kurt Busch Dale Earnhardt Jr. Rusty Wallace
TOM Ryan Newman Elliott Sadler Dale Jarrett

Daytona Should be Race 1, Rock 2nd and Las Vegas is 3rd. When I use NickName
instead of raceno it goes in alphabetical order.
Hope This Helps

Thanks for your continued support

Chris


Duane Hookom said:
What happens if you try:
TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.RaceNo, Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.RaceNo, Picks.Name
PIVOT Schedule.NickName;
--
Duane Hookom
MS Access MVP


Diggsy said:
My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




Duane Hookom said:
I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

I tried putting RaceNo in the Criteria of the columnrow as ascending. It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't sure
what
I
was doing. Any help would be great.

Chris

:

Have you tried adding RACENO to the query grid and sorting by it? Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object (table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a text
box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do you
see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted based on
criteria
from another field. The Row Heading is from a table called Name and
the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a
field
called
RACENO ascendng. This field is in both the Schedule and Picks Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
Let me get this straight... you want to sort the columns across by RaceNo? A
crosstab will automatically sort alphabetically unless you specify an
alternative order in the Column Headings property. If you display the
results of a crosstab in a form or report, it makes no difference what order
is generated by the crosstab.

You could manually set your column headings property to:
Column Headings: "Rock", "Las Vegas", " Daytona",...

--
Duane Hookom
MS Access MVP


Diggsy said:
It looks like this. Not what I need
RaceNo Name Atlanta Daytona Rock Vegas
1 CHRIS Jamie McMurray
1 JIM Bobby Labonte
1 JORDAN Rusty Wallace
1 STEVE Dale Earnhardt Jr.
1 TOM Elliott Sadler
2 CHRIS Jamie McMurray
2 JIM Greg Biffle
2 JORDAN Jamie McMurray
2 STEVE Rusty Wallace
2 TOM Dale Jarrett
3 CHRIS Dale Jarrett
3 JIM Greg Biffle
3 JORDAN Bobby Labonte
3 STEVE Kurt Busch
3 TOM Ryan Newman
4 CHRIS Bobby Labonte
4 JIM Elliott Sadler
4 JORDAN Kevin Harvick
4 STEVE Bobby Labonte
4 TOM Jimmie Johnson

When I use RaceNo in the Columnhead it looks like this

Name 1 2 3
CHRIS Jamie McMurray Jamie McMurray Dale Jarrett
JIM Bobby Labonte Greg Biffle Greg Biffle
JORDAN Rusty Wallace Jamie McMurray Bobby Labonte
STEVE Dale Earnhardt Jr. Rusty Wallace Kurt Busch
TOM Elliott Sadler Dale Jarrett Ryan Newman

The Races Go Daytona Rock Vegas
When I use the Nickname It looks like this

Name LasVegas Daytona Rock
CHRIS Dale Jarrett Jamie McMurray Jamie McMurray
JIM Greg Biffle Bobby Labonte Greg Biffle
JORDAN Bobby Labonte Rusty Wallace Jamie McMurray
STEVE Kurt Busch Dale Earnhardt Jr. Rusty Wallace
TOM Ryan Newman Elliott Sadler Dale Jarrett

Daytona Should be Race 1, Rock 2nd and Las Vegas is 3rd. When I use
NickName
instead of raceno it goes in alphabetical order.
Hope This Helps

Thanks for your continued support

Chris


Duane Hookom said:
What happens if you try:
TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.RaceNo, Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.RaceNo, Picks.Name
PIVOT Schedule.NickName;
--
Duane Hookom
MS Access MVP


Diggsy said:
My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




:

I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

I tried putting RaceNo in the Criteria of the columnrow as ascending.
It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't
sure
what
I
was doing. Any help would be great.

Chris

:

Have you tried adding RACENO to the query grid and sorting by it?
Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object
(table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a
text
box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do
you
see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted based
on
criteria
from another field. The Row Heading is from a table called Name
and
the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a
field
called
RACENO ascendng. This field is in both the Schedule and Picks
Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
That would work fine. However They race at Tracks with the same nickname
twice during the season. There are two Daytona's. 2 Atlantas and so forth..
Maybe I can give the 2nd Race another Nickname. I just thought by ordering
the column with the RaceNo would Save me time and Clerical Errors. This
database is for a friend that would not understand how to go in the crosstab
column properties. If he mispelled a NickName it would not work. I just
thought there was some sort of expression or even a WHERE condition that you
could put on the SORT ORDER> Thank.

Chris

Duane Hookom said:
Let me get this straight... you want to sort the columns across by RaceNo? A
crosstab will automatically sort alphabetically unless you specify an
alternative order in the Column Headings property. If you display the
results of a crosstab in a form or report, it makes no difference what order
is generated by the crosstab.

You could manually set your column headings property to:
Column Headings: "Rock", "Las Vegas", " Daytona",...

--
Duane Hookom
MS Access MVP


Diggsy said:
It looks like this. Not what I need
RaceNo Name Atlanta Daytona Rock Vegas
1 CHRIS Jamie McMurray
1 JIM Bobby Labonte
1 JORDAN Rusty Wallace
1 STEVE Dale Earnhardt Jr.
1 TOM Elliott Sadler
2 CHRIS Jamie McMurray
2 JIM Greg Biffle
2 JORDAN Jamie McMurray
2 STEVE Rusty Wallace
2 TOM Dale Jarrett
3 CHRIS Dale Jarrett
3 JIM Greg Biffle
3 JORDAN Bobby Labonte
3 STEVE Kurt Busch
3 TOM Ryan Newman
4 CHRIS Bobby Labonte
4 JIM Elliott Sadler
4 JORDAN Kevin Harvick
4 STEVE Bobby Labonte
4 TOM Jimmie Johnson

When I use RaceNo in the Columnhead it looks like this

Name 1 2 3
CHRIS Jamie McMurray Jamie McMurray Dale Jarrett
JIM Bobby Labonte Greg Biffle Greg Biffle
JORDAN Rusty Wallace Jamie McMurray Bobby Labonte
STEVE Dale Earnhardt Jr. Rusty Wallace Kurt Busch
TOM Elliott Sadler Dale Jarrett Ryan Newman

The Races Go Daytona Rock Vegas
When I use the Nickname It looks like this

Name LasVegas Daytona Rock
CHRIS Dale Jarrett Jamie McMurray Jamie McMurray
JIM Greg Biffle Bobby Labonte Greg Biffle
JORDAN Bobby Labonte Rusty Wallace Jamie McMurray
STEVE Kurt Busch Dale Earnhardt Jr. Rusty Wallace
TOM Ryan Newman Elliott Sadler Dale Jarrett

Daytona Should be Race 1, Rock 2nd and Las Vegas is 3rd. When I use
NickName
instead of raceno it goes in alphabetical order.
Hope This Helps

Thanks for your continued support

Chris


Duane Hookom said:
What happens if you try:
TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.RaceNo, Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.RaceNo, Picks.Name
PIVOT Schedule.NickName;
--
Duane Hookom
MS Access MVP


My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




:

I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

I tried putting RaceNo in the Criteria of the columnrow as ascending.
It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't
sure
what
I
was doing. Any help would be great.

Chris

:

Have you tried adding RACENO to the query grid and sorting by it?
Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object
(table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a
text
box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do
you
see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted based
on
criteria
from another field. The Row Heading is from a table called Name
and
the
field
is NAME. The Column Heading Is NickName taken from a table called
Schedule.
The Value is from another field called Driver from a table called
Picks.
It
looks graet. I just would like to sort the cloumn heading with a
field
called
RACENO ascendng. This field is in both the Schedule and Picks
Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
You can't easily sort the column headings. The easiest method would be to
concatenate the RaceNo with the nickname ie: [RaceNo] & "-" & [NickName].
Then, hopefully you don't get more than 9 races.

--
Duane Hookom
MS Access MVP
--

Diggsy said:
That would work fine. However They race at Tracks with the same nickname
twice during the season. There are two Daytona's. 2 Atlantas and so
forth..
Maybe I can give the 2nd Race another Nickname. I just thought by ordering
the column with the RaceNo would Save me time and Clerical Errors. This
database is for a friend that would not understand how to go in the
crosstab
column properties. If he mispelled a NickName it would not work. I just
thought there was some sort of expression or even a WHERE condition that
you
could put on the SORT ORDER> Thank.

Chris

Duane Hookom said:
Let me get this straight... you want to sort the columns across by
RaceNo? A
crosstab will automatically sort alphabetically unless you specify an
alternative order in the Column Headings property. If you display the
results of a crosstab in a form or report, it makes no difference what
order
is generated by the crosstab.

You could manually set your column headings property to:
Column Headings: "Rock", "Las Vegas", " Daytona",...

--
Duane Hookom
MS Access MVP


Diggsy said:
It looks like this. Not what I need
RaceNo Name Atlanta Daytona Rock Vegas
1 CHRIS Jamie McMurray
1 JIM Bobby Labonte
1 JORDAN Rusty Wallace
1 STEVE Dale Earnhardt Jr.
1 TOM Elliott Sadler
2 CHRIS Jamie McMurray
2 JIM Greg Biffle
2 JORDAN Jamie McMurray
2 STEVE Rusty Wallace
2 TOM Dale Jarrett
3 CHRIS Dale Jarrett
3 JIM Greg Biffle
3 JORDAN Bobby Labonte
3 STEVE Kurt Busch
3 TOM Ryan Newman
4 CHRIS Bobby Labonte
4 JIM Elliott Sadler
4 JORDAN Kevin Harvick
4 STEVE Bobby Labonte
4 TOM Jimmie Johnson

When I use RaceNo in the Columnhead it looks like this

Name 1 2 3
CHRIS Jamie McMurray Jamie McMurray Dale Jarrett
JIM Bobby Labonte Greg Biffle Greg Biffle
JORDAN Rusty Wallace Jamie McMurray Bobby Labonte
STEVE Dale Earnhardt Jr. Rusty Wallace Kurt Busch
TOM Elliott Sadler Dale Jarrett Ryan Newman

The Races Go Daytona Rock Vegas
When I use the Nickname It looks like this

Name LasVegas Daytona Rock
CHRIS Dale Jarrett Jamie McMurray Jamie McMurray
JIM Greg Biffle Bobby Labonte Greg Biffle
JORDAN Bobby Labonte Rusty Wallace Jamie McMurray
STEVE Kurt Busch Dale Earnhardt Jr. Rusty Wallace
TOM Ryan Newman Elliott Sadler Dale Jarrett

Daytona Should be Race 1, Rock 2nd and Las Vegas is 3rd. When I use
NickName
instead of raceno it goes in alphabetical order.
Hope This Helps

Thanks for your continued support

Chris


:

What happens if you try:
TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.RaceNo, Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish
ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo = Finish.RaceNo)
GROUP BY Picks.RaceNo, Picks.Name
PIVOT Schedule.NickName;
--
Duane Hookom
MS Access MVP


My Current SQL VIEW IS

TRANSFORM Max(Finish.DriverId) AS MaxOfDriverId
SELECT Picks.Name
FROM Schedule INNER JOIN (Driver INNER JOIN (Picks INNER JOIN Finish
ON
Picks.Car = Finish.Car) ON Driver.DriverId = Finish.DriverId) ON
(Schedule.RaceNo = Picks.RaceNo) AND (Schedule.RaceNo =
Finish.RaceNo)
GROUP BY Picks.Name
PIVOT Schedule.NickName;


I can post the query results and the QBE design grid if you like.

Thanks

Chris




:

I'm not sure why you add RaceNo to the "Criteria of the columnrow".

What is your current SQL view?

--
Duane Hookom
MS Access MVP
--

I tried putting RaceNo in the Criteria of the columnrow as
ascending.
It
did
not work. I even tried making a fourth column and used WHERE and
EXPRESSION
to get the sort order. Even tried adding to the SQL view. Wasn't
sure
what
I
was doing. Any help would be great.

Chris

:

Have you tried adding RACENO to the query grid and sorting by
it?
Have
you
tried anything else that you would like to share?

BTW: Name is probably the worst name you can give any object
(table,
field,
variable,...) Every object has a Name property which can be very
confusing.
Try create a new blank report based on your Name table and add a
text
box
with a control source of:
=[Name]
Save the report as "rptNameDemo" and preview the report. What do
you
see
in
the text box?

--
Duane Hookom
MS Access MVP
--

Hello I have a crosstab query that I would like have sorted
based
on
criteria
from another field. The Row Heading is from a table called
Name
and
the
field
is NAME. The Column Heading Is NickName taken from a table
called
Schedule.
The Value is from another field called Driver from a table
called
Picks.
It
looks graet. I just would like to sort the cloumn heading with
a
field
called
RACENO ascendng. This field is in both the Schedule and Picks
Table.
Any
SUggestions.
Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
I need to include a field in a crosstab query just to sort 4
update crosstab 1
Crosstab Query - Column sorting problem - Ascending 3
Crosstab query and Lookup field problem 3
CrossTab Combining 2 fields into Column heading 1
Problems with Crosstab Query 4
Select Query & Crosstab Query 1

Back
Top