Help with Access

[smartinez512]

So I've been keeping record of sports stats and picks for a group of friends and it has become overwhelming within excel. I have used a number of formulas throughout for getting the data I need and would hope there is some way to convert these formulas in access. I must preface with I have never used access before. My data set it quite extensive so I will only put a sample of what I have in the hopes of some guidance.

Here are the column headers that I have. My current data set contains approximately 25,000 rows of data going back over a year. My goal would be to be able to create a database that would allow me to mass update with an import at a future time that would update each of the member records with their pick history.

Member PickDate, PickLeague, PickGame, PickScore, PickLine, Outcome


At some point, I would like to include another set of data that would include the following:

Type, Date, Time, Pick, Visitor, Home, Line, Pick%, PickValue, Correct, Score

The second set of data would be pulled from a completely different source where the only information that would be similar or constant would be PickDate/Date, PickGame/Visitor,Home (I understand that I would most likely have to seperate PickGame in a Visitor,Home format), PickLine /Line, PickScore/Score and Outcome/Correct. As I mentioned the second set of data has no relation to the Member in the first data set. The end result would be to compare stats on Member data and results of second data set.

Any help or guidance would be greatly appreciated.

 

[ChrisM]

This is kind of tricky with no database experience, you need to put some thought in to how to design your tables and how to store your data.

I would say you start with two tables:

1. Member_Picks

2. Outcomes


Member_Picks would contain almost everything you listed:
Member, PickDate, PickLeague, PickGame, PickScore, PickLine

You only ADD data to this table, you don't delete, update, or modify any existing data. So this week you get a bunch of new picks, you work them up in Excel, then paste them in to this table.

You need to set a primary key on this table, composed of Member + PickLeague + PickGame. This ensures that each person can only bet once on each game.

Outcomes will only contain a few fields:
PickLeague, PickGame, Outcome

Again, you only ADD data to this table, as game outcomes become known.

Primary key in this table is PickLeague + PickGame. This ensures you only have one result per game.

Now you are ready to build some reports. Create a query that joins the two tables on PickLeague = PickLeague AND PickGame = PickGame

For your query columns, show everything from your original note: Member, PickDate, PickLeague, PickGame, PickScore, PickLine, Outcome