MS Access Question -- Importing Tables

Factual Questions
1

Hello!

I’m assembling a sort of team-based baseball program into Access (a kind of quasi-fantasy baseball idea based on people having teams instead of players).

Anyway, what I have is a table of MLB teams, each with ID 1-30. What I also want to do is download and import each teams 2006 schedule, which I can do through MLB.com. Once the table is imported (actually as a .csv file into Excel then into Access), is there a way for the teams in the new Access tables to be related to the ID of the team in the existing MLB table of teams?

So, for example, the Cubs table will read:

Cubs vs. Team A
Cubs vs. Team A
Cubs vs. Team A
Cubs vs. Team B

But that’s just it. I won’t be importing it via Team ID 1 vs. Team ID 2, right?

Does this make sense?

Thanks!

2

You may have to add a column to a table to allow them to join. If the data coming out of MLB.com is consistent, you can just add the key they use to your existing data.

3

I think the “best” way to do it would be to set up a couple additional tables, but for this project you can get away with doing it more simply.

The simpler way to do it will be to make the team’s name be the primary key for your team table. This is bad design, but for a project like this, it’s probably acceptable bad design.

Then create a table with games: this table will have a primary key for the game, the date, the location, and fields for home team and away team.

I’m not sure how you’re going to import the data into this table. Does mlb.com offer the home/away information? If so, import only the home team information (that’ll keep you from importing each game twice, once for the home team and once for the away team). If not, things get more complicated.

Anyway, once you’ve imported that information, you should be able to set up a query that displays all game records in which the home team OR the away team equals the current team record displayed on the form on which you’ll use the query. Set up a subform on the team form, and you should be good to go.

Daniel

4

Couldn’t you either add a column in EXCEL with the team ID or just replace the names in EXCEL with the team ID?

AM I reight in assuming that the home team is in one column and the visiting team in another? If so you can create a lookup table in EXCEL that associates the team with an ID and then in seperate columns put in the team IDs with a VLOOKUP function. Once you’ve done that you can then import that into ACCESS.