starting a new database maybe bit off more than I can chew

[flippen]

ok I finally am getting off my duff and jumping in with two feet.

Hopefully this thread will help as a tutorial for myself and others.

I am going to try and work a Db that comprises 5 different leagues comprising about 150 players that can play up to 7 games(5 types)each per week across a 15 week season, 2 seasons per year. 2 types of games will be played with a partner while the other 3 types are played solo.

Getting the basics down

Built tables as follows:
Season Table (1 column table)
week Table (1 column table)
League table (1 column table)
Team Table (1 column table)
Player Table
This includes:
column that has all player names
column that use Lookup wizard to Team table
column that use Lookup wizard to League table
column that use Lookup wizard to Week Table
column that use Lookup wizard to season Table
Current elo rating

So far so good just a lot of inputing.

Now to get a little fancy.
I will create a Game table.
It will Include Player
Game
opponent
opponant Elo rating
partner
season
Week
Win (yes/no)

first thing:
Use a lookup wizard for opponent
would like to use a query? to do the lookup wizard
Select team and be able to pick from a list the team members



Use lookup wizard for partner (would like it to see current player's team and propogate a list based upon that).

I will stop at this point and hopefully get some answers.

I will also be adding to this thread in hopes that Someone will be abl to travel down this path and see how the design process, pitfalls, creative solutions all come together to make a successful database.

[maximus]

get professional help. Coz no point in doing some thing that you don't fully understand. People in this forum will help you with definite problems, but to handle the development of a database you need to have a definite programming knowledge. Don't mind me saying this but you will soon realize the problem.

[flippen]

Max,

I appreciate your frankness.

To clarify, Yes I want quite a bit.

I know I am new, I have always been hesitant about giving a lot of higher end info to a new person on some other boards I frewuent (Excel, and phonesystems).

So if I go about this a different way:


How do you set up a query that references a field then references another field based upon the selection of the first field? Or is this possible?
I know how I would query if I knew both parameters and felt like typing them in, but I want this a little bit more user friendly when inputing 450 entries every week. Perhaps input in this way is not feesable then I will need to write a VBA script to accomplish this. Since this is for fun (yes I have a sick sense of what is fun) paying for professional help (although professionl help may be what I need if I think DB management might be fun, but it would involve a couch) is not an option.

[TheShabz]

Your basic SELECT query goes as such,
SELECT myfield1, myfield2 ----these are the fields you want to display
FROM mytable ----where you are getting the info from (Joins are applied here)
WHERE myfield1 = 3 and myfield1 > myfield2 ----just some examples of criteria for the query

example:
I want a list of all games that playerA won.

SELECT PlayerName, GameName
FROM Game
WHERE PlayerName = "PlayerA" AND Result = "Win"

thats just something basic. There's plenty of free information out on the interwebs. A simple Google search will get you started off on the right foot. If this is something you are using for educational purposes, go ahead and try to figure it out on your own. If it's something for a legitimate league you are looking to create this for, seek professional help.

[flippen]

Thanks,

that is a good start, I still need to get accostomed to the verbage. and the flow.

Yes this is all for my own learning. Our league already has a really good DB manager, this si just something that I want to get up to speed on.

Ultimate goal is to incorporate an Elo rating system. but I need to get DB in place first. Excel is just too clunky for this task.

Since there is a Database in place I am able to double check my progress against what is in place. I even had a table in place that he failed to implement until 2 season ito it.
Word of advice ot the new builders like me: Map Map and then Map again everything you want in the database and then think of everything you could possibly want to do with it.

The hardest part I am finding is that you can't have too many tables. My first few attempts had too much info in each table and it made inputting data a whole lot harder. Using the lookiup in a field that looks at a much smaller table has been a blessing

[maximus]

Another important aspect that you have to look very careful into is the relationship between the tables. in a multiple table structure the realationships are very important. Flippen I hope you didn't mind me saying to seek professional advice coz I didn't know that you were doing it to learn access. I am there to help in what ever I can. My knowledge may not be as much as most of the people but i will certainly try to help you out to the best of my capacity.

[evander]

Flippen:

Building a database starts with what I call "data appreciation" or appreciation of data (men, English is such a difficult language). You've got to understand what purpose your data is going to give you, and how it should behave (errr... okay I'm a bit weird).

In my humble experience, I started to appreciate data by learning Excel, particularly VLOOKUP and PivotTable. I train people having no background in Microsoft Access, and still they easily grasp database concepts like relationships and SQL aggregate queries. I simply tell them that relationships are like VLOOKUP and so on...

Try to model your database in Excel first. Start with creating lists (or in Excel 2007, "tables").

Good luck.

[flippen]

Once agian I appreciate the responses. to morrow i will be able sit down and start compiling some queries. I will keep you posted on my successes and failures.