How to edit the Lahman Database? - Microsoft Access Questions

[sbrylski]

I'd like to edit the Lahman database, and looking for help on how.

 

I have two things I want to do:

1. Delete everybody who has not played for a Milwaukee based major league team.

2. Edit birthdates and debut years.

 

It doesn't sound like it should be too complicated to do, however I have extremely limited exposure to Microsoft Access. I am, however, fairly computer handy, so I was wondering if anyone could walk me through the basics on how to accomplish what I want to do.

 

Can anyone help me? (Or link me to an online reference that would help?)

 

EDIT: Even just brief steps, you don't have to explain the terminology. I'll figure it out.

[rluzinski]

I use Excel when messing with the lahman database. It's certainly more combersome than Access but it can be done. I use "Vlookup" to combine different tables and "sumif" to total up a specific player's career stats from seasonal stats. Throw in some filters and sorts and I generally can fumble my way to whatever I'm looking for. I'm sure that a database guy would cringe at that solution but I'll help you if you want to go that way.

[sbrylski]

Isn't the problem with Excel is that it can't handle the full "batting" and "pitching" files?

[rotoherb]

Are you trying to delete the player stats or do you just want to delete the players from the master list of players? Editing the debuts and birth dates is easy. Just open the database in access and double click on the "Master" table. You just need to find the players that you want to edit and then you can edit the values manually. If you want to delete all of the other players first then I can give you some queries to run, I just need to know if you want to delete all of the player stats or if you just want to delete those players from the Master table.

[sbrylski]

Are you trying to delete the player stats or do you just want to delete the players from the master list of players? Editing the debuts and birth dates is easy. Just open the database in access and double click on the "Master" table. You just need to find the players that you want to edit and then you can edit the values manually. If you want to delete all of the other players first then I can give you some queries to run, I just need to know if you want to delete all of the player stats or if you just want to delete those players from the Master table.

I think deleting the non-Milwaukee players off just the Master table would be sufficient enough for my purposes. And yeah, editing the birthdates shouldn't be difficult, I understand how to do that.

 

So you can help though?

 

[sbrylski]

Alright, screwing around with it myself, this is where I'm at: I have two tables, the "Master" table and the "Batting" table in which I removed all non-Milwaukee player lines.

 

So, I want to filter out the players on the "Master" table that do not also appear on the "Batting" table. What I'm thinking is I need to run a query off the "Master" table and use the name field from the "Batting" table as my criteria for the name field on the query, if that makes sense. Does anyone know how to do this?

 

EDIT: Or maybe actually apply a filter to the Master table?

[rotoherb]

you can open a query window and run these queries one at a time to delete every player who never played on a milwaukee team:

 

delete from master where playerid not in (select playerid from batting where teamid in ('mil','ml1','ml2','ml3','ml4','mlu','se1'))

delete from master where playerid not in (select playerid from pitching where teamid in ('mil','ml1','ml2','ml3','ml4','mlu','se1'))

 

It may take 30 minutes or more to run the queries, but it should work. There are faster ways to do it, but you'd need to create a table first and do some inserts. The queries above will get the job done. I left in any milwaukee team, including the Pilots, I'm not sure if that's what you want. If you just want the Brewers then take out all of the teams except for 'MIL'. It's also a good idea to make a copy of the database before you run those queries, just in case it's not what you're looking for.

[sbrylski]

Thanks. But...

 

I ran the first one earlier tonight (I hope I did it right) and I let it go for about two hours, and I got impatient. So now its running again, I'll run it overnight. Hopefully it works. (My cpu is decent, I wouldn't expect below average preformance.)

[sbrylski]

Hmm, I must have did it wrong. I ended up with a blank sheet. I'll have to try again later today.

[sbrylski]

Okay, it would help if I was running a "delete query" I suppose. But even then, a direct copy and paste of those lines above I get "You are about to delete 0 records". I fiddled around with the line and tried just "not in (select playerid from batting where teamid in ('mil','ml1','ml2','ml3','ml4','mlu','se1'))" and that's running now... not sure if its running correctly, however.

[rotoherb]

wow, I really didn't think it would take so long to run that delete statement, but it's not entirely surprising. OK, the following process will work a lot faster, you just need to be able to create a table, which I think you can handle. I just ran through this and it looks like it works to me.

 

1) Double click on "create table in design view" and create a table with one field called "playerid". Make it a text column with a field size of 10. Save the table as "toDelete".

 

2) Click on "Queries" under "Objects". Double click on "create query in design view". Close the small "show table" window that comes up, you don't need that. Right click on the Query window and select "SQL View".

 

3) Copy the following statements into that window and run them one at a time by hitting the exclamation mark in the toolbar.

 

insert into toDelete (playerid) select distinct playerid from batting where teamid not in ('mil','ml1','ml2','ml3','ml4','mlu','se1')

insert into toDelete (playerid) select distinct playerid from pitching where teamid not in ('mil','ml1','ml2','ml3','ml4','mlu','se1')

 

What you are doing here is inserting every player who never played for a milwaukee team into that table.

 

4) Copy and paste the following statements one at a time into the window and run them.

 

delete from toDelete where playerid in (select playerid from batting where teamid in ('mil','ml1','ml2','ml3','ml4','mlu','se1'))

delete from toDelete where playerid in (select playerid from pitching where teamid in ('mil','ml1','ml2','ml3','ml4','mlu','se1'))

 

What you are doing is first inserting every player who didn't play their entire career with a milwaukee team into the toDelete table. Then in step four you are deleting all of the players who played only part of their careers in Milwaukee. What you are left with is a list of every player who never played a day for a milwaukee team.

 

5) Run this query to delete all of those player from the Master table.

 

delete from master where playerid in (select playerid from toDelete)

 

This should leave you with about 1000 rows in the Master table. These queries won't delete all of the managers. If you want to delete the manager then I'd suggest just opening the master table at this point and sorting by playerid. Then you can highlight all of the rows without a playerid and hit the delete key to delete them. The result is a Master table with 868 players. Again, I'd recommend making a copy of the database before you do any of this.

[sbrylski]

Success, it was a breeze! Thanks so much, roto.

 

(Btw, the first query did work eventually, it just took about 3 hours. The second set of instructions took ~5 minutes. Thanks for taking the time to write them out for me.)