Query using crosstab data

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the finish of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called TOP 10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris
 
This should give you the top 5 and 10 but I don't know how you determine
DNF.
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10]
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;
 
Duane,
Thank You for your help. As far as The DNF column, I was
trying to add up how many times the status was not equal
to the word running. Maybe that will help explain. i
appresiate your help. The other part works great. Thank
you,

Chris
-----Original Message-----
This should give you the top 5 and 10 but I don't know how you determine
DNF.
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10]
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;


--
Duane Hookom
MS Access MVP


I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the finish of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called TOP 10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris
Click to expand...


.
Click to expand...
 
The sql I provided should have given you a clue how to "count" an expression
by using sum. To add the DNF column, try:
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10],
Sum(Abs([Status]<>"Running")) AS DNF
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;

--
Duane Hookom
MS Access MVP


Chris said:
Duane,
Thank You for your help. As far as The DNF column, I was
trying to add up how many times the status was not equal
to the word running. Maybe that will help explain. i
appresiate your help. The other part works great. Thank
you,

Chris
-----Original Message-----
This should give you the top 5 and 10 but I don't know how you determine
DNF.
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10]
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;


--
Duane Hookom
MS Access MVP


I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the finish of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called TOP 10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris
Click to expand...


.
Click to expand...
Click to expand...
 
Chris said:
I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the finish of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called TOP 10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris
Click to expand...


I'm pretty sure this isn't *exactly* what you want (but I don't understand
the structure of the Pick or Finish tables, or how the backend of a betting
pool works). But it does assemble the information I think you want.


CREATE TABLE Finish
(RaceNo INTEGER
,Name CHAR(48)
,Finish INTEGER
,CONSTRAINT pk_Finish PRIMARY KEY (RaceNo, Name)
)


Sample Data


In the Finish column, 0 = DNF.

1, Mike, 1
2, Mike, 5
3, Mike, 10
4, Mike, 0
1, Dave, 20
2, Dave, 25
3, Dave, 0
4, Dave, 0
1, Anna, 2
2, Anna, 1
3, Anna, 4
4, Anna, 6



TRANSFORM COUNT(F1.Finish)
SELECT F1.Name
FROM Finish AS F1
GROUP BY F1.Name
PIVOT SWITCH((F1.Finish >= 1 AND F1.Finish <= 5), "Top 5",
(F1.Finish >= 1 AND F1.Finish <= 10), "Top 10",
(F1.Finish = 0), "DNF") IN ("Top 5", "Top 10", "DNF")

Output

Name Top5 Top10 DNF
Anna, 3, 4,
Dave, , , 2
Mike, 2, 3, 1
 
Thanks Duane,
I tried it and it worked fine. Just curious..What does the
Abs function do? Thank You for everything.

Chris
-----Original Message-----
The sql I provided should have given you a clue how to "count" an expression
by using sum. To add the DNF column, try:
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10],
Sum(Abs([Status]<>"Running")) AS DNF
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;

--
Duane Hookom
MS Access MVP


Duane,
Thank You for your help. As far as The DNF column, I was
trying to add up how many times the status was not equal
to the word running. Maybe that will help explain. i
appresiate your help. The other part works great. Thank
you,

Chris
-----Original Message-----
This should give you the top 5 and 10 but I don't know how you determine
DNF.
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10]
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;


--
Duane Hookom
MS Access MVP


I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the
Click to expand...
finish
of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called
Click to expand...
TOP
10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris


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


.
Click to expand...
 
absolute value

--
Duane Hookom
MS Access MVP


Chris said:
Thanks Duane,
I tried it and it worked fine. Just curious..What does the
Abs function do? Thank You for everything.

Chris
-----Original Message-----
The sql I provided should have given you a clue how to "count" an expression
by using sum. To add the DNF column, try:
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10],
Sum(Abs([Status]<>"Running")) AS DNF
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;

--
Duane Hookom
MS Access MVP


Duane,
Thank You for your help. As far as The DNF column, I was
trying to add up how many times the status was not equal
to the word running. Maybe that will help explain. i
appresiate your help. The other part works great. Thank
you,

Chris
-----Original Message-----
This should give you the top 5 and 10 but I don't know
how you determine
DNF.
SELECT SampleQuery.Name,
Sum(Abs([Finish]<=5)) AS [Top 5],
Sum(Abs([Finish]<=10)) AS [Top 10]
FROM SampleQuery
GROUP BY SampleQuery.Name
ORDER BY Sum(Abs([Finish]<=5)) DESC;


--
Duane Hookom
MS Access MVP


message
I have a database that handles a racecar driver picks in
a friendly pool. Here is a query called Points that I
built from 2 tables called Picks and Finish. It displays
the Name, the race# , the driver picked and the finish
of
that driver. There are over 30 races in the season. I am
trying to figure a way to modify or create a seperate
query to add up how many times a players(Name) picks
finishes in the top 5. I would like to name the column
TOP5 I also would like a column that tells me how many
times a player picks finishes in the top 10 called TOP
10.
The last thing I need is a column that tells me how many
times the status is not running called DNF.

I tried to do a crosstab query but it did not work
right.
I have a sample of the query below.

Name RaceNo DriverId Finish Car Points Status

JESSE 1 Kevin Harvick 4 29 165 Running
MIDGE 1 Jimmie Johnson 5 48 160 Running
LC 1 Joe Nemechek 6 01 150 Running
AMBER 1 Elliott Sadler 7 38 146 Running
AL 1 Dale Jarrett 10 88 134 Running
JIM 1 Bobby Labonte 11 18 130 Running
JESSE 2 Matt Kenseth 1 17 190 Running
MIDGE 2 JamieMcMurray 3 42 170 Running
AMBER 2 Jamie McMurray 3 42 170 Running
JIM 2 Dale Jarrett 40 88 43 Engine
AL 2 Ryan Newman 6 12 155 Running
LC 2 Jeff Gordon 10 24 139 Running
JESSE 3 Kasey Kahne 2 9 175 Running
AL 3 Kasey Kahne 2 9 175 Running
AMBER 3 Kurt Busch 9 97 143 Running
JIM 3 Greg Biffle 40 16 43 Engine
MIDGE 3 Kurt Busch 9 97 143 Running
LC 4 Bobby Labonte 18 18 109 Running
JESSE 4 Tony Stewart 7 20 156 Running
AMBER 4 Tony Stewart 7 20 156 Running
LC 4 Casey Mears 34 41 66 Engine
JIM 4 Elliott Sadler 29 38 76 Running
MIDGE 4 Tony Stewart 7 20 156 Running
AL 4 Brian Vickers 21 25 100 Running

This is a small sample of what I would like my query to
look like.

Name Top 5 Top 10 DNF
Jesse 3 4 0
Midge 2 2 0
LC 0 2 1

I appreciate any help you can offer.

Thanks Chris


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top