Trouble with Access and the Lahman Database - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Trouble with Access and the Lahman Database

Moderator: Baseball Moderators

Trouble with Access and the Lahman Database

Postby Bloody Nipples » Tue Dec 28, 2004 7:06 am

First of all, let me just say that the Lahman Database is awesome. Also, many thanks to Tavish and Pedantic whose tutorial thread I just read. However, I still have a couple questions. So, here they are:

1) How would I do the common .17/.33/.50 weighted three year average using the Lahman Database? I was able to do a simple, unweighted average, but that didnt help too much. What I couldnt do was separate each player's stat into separate years so that I could do the weighted part. And when I tried to modify the Batting table so that I could have separate tables for each year, it wouldnt work.

2) How do you enter an expression in the Design View? I tried a couple different ways, including with the expression builder, but each time I tried to switch to a different view I got an error message that my expression's syntax was erroneous.

3) When I finally get that weighted average done, how might I take the results and input them into a ranking formula? Would I use excel for that? If so, how would I export the weighted average querie to excel?


If anybody can answer these questions, I will be forever in debt to them. Thanks a ton in advance. ;-D
Image
aka enderwiggin
Bloody Nipples
General Manager
General Manager


Posts: 3745
Joined: 28 Feb 2004
Home Cafe: Baseball
Location: doing my laundry...

Postby trevisc » Tue Dec 28, 2004 8:55 am

welcome to the world of programming!

What I like to do in Access is create my Query and then in Design mode go to View and then SQL view. This shows you the actually SQL code that is generated by that Query. then you can port it over to Excel if you need to. Access has a ton of built in functions to help you with math though so you should be able to do everything from within there.

For example:

I have a query in my Lahman's Database called '2004 HR Leaders'

If I go to SQL View here is what the query looks like:

SELECT Master.nameLast, Master.nameFirst, Sum(Batting.HR) AS HR, Batting.yearID
FROM Batting INNER JOIN Master ON Batting.playerID = Master.playerID
GROUP BY Master.nameLast, Master.nameFirst, Batting.yearID, Batting.playerID
HAVING (((Batting.yearID)=2004))
ORDER BY Sum(Batting.HR) DESC;

in your case you'd probably want to build 3 fields one for each stat multiplied by the percentage (.50,.33,.17). Call the first field "First Year", etc etc.

I went ahead and did one so I could help you get started. What I did was create 3 queries for each year of all HRs: 2004, 2003, 2002

I have a field in each query that is the HR total multiplied by the percentage. Then I built another Query called 2005 Projections that takes the 3 fields (one from each year) and adds them together.

When I did this I got the following projections:

nameLast nameFirst Projections
Pujols Albert 45.01
Bonds Barry 45
Thome Jim 43.65
Ramirez Manny 41.02
Edmonds Jim 41.01
Beltre Adrian 39.75
Dunn Adam 39.73
Rodriguez Alex 39.63
Ortiz David 37.7
Sheffield Gary 36.99
Sosa Sammy 36.65
Delgado Carlos 35.3
Guerrero Vladimir 34.38
Teixeira Mark 34.04
Konerko Paul 33.41
Alou Moises 33.39
Helton Todd 32.33
Rolen Scott 32.02
Tejada Miguel 31.69
Lee Derrek 31.67
Jones Andruw 31.31
Soriano Alfonso 31.3
Blalock Hank 31.01
Lee Carlos 31
Bagwell Jeff 30.96
Burnitz Jeromy 30.73
Castilla Vinny 30.71
Huff Aubrey 30.65
Batista Tony 30.02
Lopez Javy 29.6
Valentin Jose 29.34
Burnitz Jeromy 29.08
Ramirez Aramis 29.07
Jones Chipper 29.01
Chavez Eric 29
Lowell Mike 28.65
Berkman Lance 28.35
Ramirez Aramis 28.08
Palmeiro Rafael 27.95
Wilkerson Brad 27.71
Boone Bret 27.63
Jenkins Geoff 27.33
Abreu Bobby 26.7
Wells Vernon 26.3
Cabrera Miguel 26.07
Matsui Hideki 26.05
Cameron Mike 26.04
Thomas Frank 25.92
Drew J.D. 25.72
Guillen Jose 25.68
Wilson Craig 25.37
Kent Jeff 25.35
Green Shawn 25.03
Sanders Reggie 24.97
Blake Casey 24.37
Pena Carlos 24.03
Hunter Torii 23.99
Beltran Carlos 23.99
Posada Jorge 23.97
Hafner Travis 23.38
Hidalgo Richard 23.31
Mora Melvin 23.04
Burrell Pat 23.01
Finley Steve 22.67
Beltran Carlos 22.63
Gonzalez Alex 22.01
Nevin Phil 21.71
Durazo Erubiel 21.67
Young Dmitri 21.63
Giambi Jason 21.57
Koskie Corey 21.37
Jones Jacque 21.36
Gonzalez Alex 21.35
Grissom Marquis 21.34
Biggio Craig 21.03
Finley Steve 20.97
Sexson Richie 20.88
Guillen Jose 20.73
Casey Sean 20.7
Giles Brian 20.69
Cruz Jr. Jose 20.67
Hidalgo Richard 20.42
Patterson Corey 20.37
Martinez Tino 20.36
Crede Joe 20.34
Varitek Jason 20.31
Millar Kevin 20.31
Sweeney Mike 20.02
Feliz Pedro 20.02
Beltran Carlos 19.99
Gonzalez Luis 19.97
Williams Bernie 19.69
Monroe Craig 19.65
Young Mike 19.36
Pena Wily Mo 19.07
Hernandez Ramon 18.99
Anderson Garret 18.95
Gonzalez Alex 18.78
Jeter Derek 18.71
Uribe Juan 18.71
Munson Eric 18.67
White Rondell 18.67
Stairs Matt 18.66
Beltran Carlos 18.63
Gonzalez Alex 18.61
Bay Jason 18.41
Lawton Matt 18.35
Gonzalez Alex 18.12
Gonzalez Alex 18.1
Mench Kevin 18.08
Rowand Aaron 18.06
Rodriguez Ivan 18.01
Floyd Cliff 18
Gonzalez Alex 17.95
Bay Jason 17.75
Griffey Jr. Ken 17.69
Finley Steve 17.67
Gonzalez Alex 17.44
Byrnes Eric 17.36
Damon Johnny 17.36
Ginter Keith 17.35
Glaus Troy 17.34
Piazza Mike 17.03
Dye Jermaine 16.73
Giles Brian 16.73
Broussard Ben 16.67
Ibanez Raul 16.66
Bradley Milton 16.03
Polanco Placido 16.01
Clark Tony 16
Finley Steve 15.97
Martinez Edgar 15.96
Wilson Preston 15.9
Martinez Victor 15.74
Guillen Carlos 15.71
Lieberthal Mike 15.68
Hillenbrand Shea 15.66
Ordonez Magglio 15.6
Johnson Charles 15.31
Loretta Mark 15.01
Encarnacion Juan 14.98
Ensberg Morgan 14.95
Hidalgo Richard 14.81
Crosby Bobby 14.74
LaRue Jason 14.66
Phelps Josh 14.64
Gerut Jody 14.63
Payton Jay 14.6
Baldelli Rocco 14.35
Walker Todd 14.34
Furcal Rafael 14.33
Vidro Jose 14.33
Conine Jeff 14.33
Mueller Bill 14.31
Gibbons Jay 14.29
Morneau Justin 14.05
White Rondell 14.05
Durham Ray 14.03
Barrett Michael 14.02
Hatteberg Scott 14.01
Hinske Eric 14.01
Phelps Josh 13.45
Bell David 13.38
Mackowiak Rob 13.37
Sierra Ruben 13.37
Encarnacion Juan 13.28
Nixon Trot 13.26
Bigbie Larry 13.02
Winn Randy 13.01
Harvey Ken 13
Tucker Michael 13
Klesko Ryan 12.96
Choi Hee Seop 12.69
Jimenez D'Angelo 12.66
Higginson Bobby 12.66
Walker Larry 12.65
Garciaparra Nomar 12.59
Gonzalez Alex 12.51
Garciaparra Nomar 12.42
Sierra Ruben 12.38
Kotsay Mark 12.36
Giles Marcus 12.29
Garciaparra Nomar 12.09
Dellucci David 12.05
Bellhorn Mark 12.05
Overbay Lyle 12.04
Nix Laynce 12.02
Rollins Jimmy 12.02
Gonzalez Alex 12.01
Spiezio Scott 11.98
Hidalgo Richard 11.92
Garciaparra Nomar 11.92
Gonzalez Alex 11.85
Walker Larry 11.8
Dellucci David 11.72
Wigginton Ty 11.67
Alfonzo Edgardo 11.66
LeCroy Matt 11.64
Bellhorn Mark 11.39
Werth Jayson 11.38
Gonzalez Alex 11.35
Hernandez Jose 11.35
Inge Brandon 11.35
Molina Ben 11.32
Gonzalez Juan 11.27
Phelps Josh 11.14
Crisp Coco 11.04
Ford Lew 11.04
Barajas Rod 11.04
Hillenbrand Shea 11.04
Conine Jeff 11.03
Hudson Orlando 11.01
Schneider Brian 11.01
Podsednik Scott 11.01
Pierzynski A.J. 11
Renteria Edgar 10.99
Kennedy Adam 10.99
Kearns Austin 10.98
Berroa Angel 10.97
Infante Omar 10.72
Greene Khalil 10.71
Snow J.T. 10.68
Belliard Ron 10.68
Randa Joe 10.64
Gonzalez Alex 10.51
Wigginton Ty 10.48
Rivera Juan 10.35
Wilson Jack 10.34
Branyan Russ 10.34
Fullmer Brad 10.34
Helms Wes 10.27
Walker Larry 10.15
Choi Hee Seop 10.14
Ward Daryle 10.05
Johnson Reed 10
Greene Todd 10
Encarnacion Juan 9.98
Phelps Josh 9.95
Gonzalez Alex 9.85
Hernandez Jose 9.7
Mabry John 9.7
Stewart Shannon 9.68
Suzuki Ichiro 9.65
Lugo Julio 9.64
Cabrera Orlando 9.63
Lamb Mike 9.38
Hernandez Jose 9.37
Utley Chase 9.37
Cuddyer Mike 9.36
Stewart Shannon 9.35
Rivas Luis 9.34
Hall Toby 9.32
Johnson Nick 9.31
Cruz Deivi 9.31
Walker Larry 9.3
Everett Carl 9.29
Cabrera Orlando 9.29
Gonzalez Alex 9.28
Gonzalez Alex 9.11
Melhuse Adam 9.02
Crawford Carl 9.02
Lo Duca Paul 9.01
Miller Damian 9
Clayton Royce 8.99
Spivey Junior 8.98
Ledee Ricky 8.98
Gonzalez Alex 8.78
Everett Carl 8.78
Valent Eric 8.71
Bautista Danny 8.69
Matthews Gary 8.69
Blum Geoff 8.66
Davanon Jeff 8.65
Cabrera Orlando 8.63
Gonzalez Alex 8.62
Gonzalez Alex 8.61
Gonzalez Alex 8.6
Gonzalez Alex 8.45
Michaels Jason 8.35
Phillips Jason 8.32
Matos Luis 8.31

I hope that helps. If you want me to email you the database let me know where to send them and i'll just put the queries in an empty database that you can import into your Lahman Database.

Have fun!
trevisc
Hall of Fame Hero
Hall of Fame Hero

User avatar
Fantasy ExpertCafe Ranker
Posts: 11271
Joined: 6 Jan 2003
Home Cafe: Baseball
Location: Fantasy Baseball Cafe 2004,2005 Keeper League Champion

Postby Bloody Nipples » Tue Dec 28, 2004 3:29 pm

Thanks for the help, trev. ;-D

I went ahead and did one so I could help you get started. What I did was create 3 queries for each year of all HRs: 2004, 2003, 2002

I have a field in each query that is the HR total multiplied by the percentage. Then I built another Query called 2005 Projections that takes the 3 fields (one from each year) and adds them together.


This is the part I get hung up on. How exactly would I do this? Where is the field in each querie? Also, how do you get individual years?

Also, does what youre saying mean I can create a querie using a table created by another querie?
Image
aka enderwiggin
Bloody Nipples
General Manager
General Manager


Posts: 3745
Joined: 28 Feb 2004
Home Cafe: Baseball
Location: doing my laundry...

Postby trevisc » Tue Dec 28, 2004 3:30 pm

Bloody Nipples wrote:Thanks for the help, trev. ;-D

I went ahead and did one so I could help you get started. What I did was create 3 queries for each year of all HRs: 2004, 2003, 2002

I have a field in each query that is the HR total multiplied by the percentage. Then I built another Query called 2005 Projections that takes the 3 fields (one from each year) and adds them together.


This is the part I get hung up on. How exactly would I do this? Where is the field in each querie? Also, how do you get individual years?

Also, does what youre saying mean I can create a querie using a table created by another querie?


Holy cow man! I can't give you a full lesson on Access database programming..send me a PM on where to send you this database and you can just see how I did it...
trevisc
Hall of Fame Hero
Hall of Fame Hero

User avatar
Fantasy ExpertCafe Ranker
Posts: 11271
Joined: 6 Jan 2003
Home Cafe: Baseball
Location: Fantasy Baseball Cafe 2004,2005 Keeper League Champion

Postby Bloody Nipples » Tue Dec 28, 2004 3:36 pm

trevisc wrote:
Bloody Nipples wrote:Thanks for the help, trev. ;-D

I went ahead and did one so I could help you get started. What I did was create 3 queries for each year of all HRs: 2004, 2003, 2002

I have a field in each query that is the HR total multiplied by the percentage. Then I built another Query called 2005 Projections that takes the 3 fields (one from each year) and adds them together.


This is the part I get hung up on. How exactly would I do this? Where is the field in each querie? Also, how do you get individual years?

Also, does what youre saying mean I can create a querie using a table created by another querie?


Holy cow man! I can't give you a full lesson on Access database programming..send me a PM on where to send you this database and you can just see how I did it...


:-D OK, thanks. I'll pm you right now
Image
aka enderwiggin
Bloody Nipples
General Manager
General Manager


Posts: 3745
Joined: 28 Feb 2004
Home Cafe: Baseball
Location: doing my laundry...

Postby Amazinz » Tue Dec 28, 2004 3:50 pm

Bloody Nipples wrote:This is the part I get hung up on. How exactly would I do this? Where is the field in each querie? Also, how do you get individual years?

SELECT (HR*0.17) FROM Batting WHERE Batting.yearID=2004;

Bloody Nipples wrote:Also, does what youre saying mean I can create a querie using a table created by another querie?


Yes you can query other queries. You can even do it inline if you want.

Example:

SELECT HR FROM Batting WHERE yearID=2004 AND playerID IN (SELECT playerID FROM Fielding WHERE Pos="1B");
Image
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

Postby LBJackal » Tue Dec 28, 2004 4:24 pm

Another note that doesn't have anything to do with Access, but you might want to take HR/AB * [(AB/G)*162] instead of using plain old HR. So if somebody missed significatn time in a season it wouldn't hurt their 3 year average. You could do that for all the counting stats I suppose. I think it would make for more accurate rankings. You'd also want to have a minimum GP for a season so you aren't projecting a 30 game sample size or something, that would also skew the stats. Just some suggestions... it makes it a lot more difficult but once you figure the basic way out, that might make it even more accurate. I wouldn't know how to do this in Access, but hey maybe you'll learn how !+)
Image

"Jack, will you call me, if you're able?"

"I've got your phone number written, in the back of my Bible."
LBJackal
Hall of Fame Hero
Hall of Fame Hero

User avatar
Pick 3 Weekly Winner
Posts: 9196
Joined: 1 Jul 2003
Home Cafe: Baseball
Location: The Hotel Yorba

Postby Amazinz » Tue Dec 28, 2004 5:14 pm

Pain in the butt to do it through the Access designer but the SQL would be something along these lines:

SELECT
Master.nameLast,
Master.nameFirst,
Batting.G,
Batting.AB,
Batting.HR,
IIF(Batting.AB>0,ROUND((Batting.HR/Batting.AB)*(Batting.AB/Batting.G)*162),0) AS "Adjusted HR"

FROM Batting INNER JOIN Master ON Batting.playerID = Master.playerID

WHERE Batting.yearID=2004 AND Master.playerID NOT IN
(SELECT Fielding.playerID FROM Fielding WHERE Fielding.yearID=2004 AND Fielding.POS="P");
Image
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

Postby LBJackal » Tue Dec 28, 2004 5:25 pm

Amazinz wrote:Pain in the butt to do it through the Access designer but the SQL would be something along these lines:

SELECT
Master.nameLast,
Master.nameFirst,
Batting.G,
Batting.AB,
Batting.HR,
IIF(Batting.AB>0,ROUND((Batting.HR/Batting.AB)*(Batting.AB/Batting.G)*162),0) AS "Adjusted HR"

FROM Batting INNER JOIN Master ON Batting.playerID = Master.playerID

WHERE Batting.yearID=2004 AND Master.playerID NOT IN
(SELECT Fielding.playerID FROM Fielding WHERE Fielding.yearID=2004 AND Fielding.POS="P");


Wow, good thing there are people like you out there :-° that woulda taken me years to figure out!
Image

"Jack, will you call me, if you're able?"

"I've got your phone number written, in the back of my Bible."
LBJackal
Hall of Fame Hero
Hall of Fame Hero

User avatar
Pick 3 Weekly Winner
Posts: 9196
Joined: 1 Jul 2003
Home Cafe: Baseball
Location: The Hotel Yorba

Postby Bloody Nipples » Tue Dec 28, 2004 7:00 pm

Thanks guys. I've learned a lot, and I've gotten to the point where I have the weighted average of all the stats I want (later I will try LBJ's suggestion) in a table with the players' names and positions. But there is one problem: there are multiple entries for each name, some with the same position and some not; and the entries for each player have different stats. Whats going on here?

If it helps you figure it out, I could send you my queries. That is, if you want to be really cool. :-D
Image
aka enderwiggin
Bloody Nipples
General Manager
General Manager


Posts: 3745
Joined: 28 Feb 2004
Home Cafe: Baseball
Location: doing my laundry...

Next

Return to Baseball Leftovers

Who is online

Users browsing this forum: buiviopaufamp, unioreimi and 8 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Wednesday, Aug. 27
(All times are EST, weather icons show forecast for game time)

St. Louis at Pittsburgh
(12:35 pm)
Texas at Seattle
(3:40 pm)
Tampa Bay at Baltimore
(7:05 pm)
Washington at Philadelphia
(7:05 pm)
Boston at Toronto
(7:07 pm)
NY Yankees at Detroit
(7:08 pm)
Atlanta at NY Mets
(7:10 pm)
Chi Cubs at Cincinnati
(7:10 pm)
Cleveland at Chi White Sox
(8:10 pm)
Oakland at Houston
(8:10 pm)
Minnesota at Kansas City
(8:10 pm)
Milwaukee at San Diego
(9:10 pm)
LA Dodgers at Arizona
(9:40 pm)
Miami at LA Angels
(10:05 pm)
Colorado at San Francisco
(10:15 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact