Multiple queries of the same table

[eieio]

I have a table which has the following fields:
Match,Game,Player,Round,and Score.

What I need to be able to do is to have one query which will allow me to list ONE time the Match,Game and Player and also allow me to list 5 different Rounds and Scores.

For example:

Match, Game, Player, Round1, Round1Score, Round2, Round2Score, Round3, Round3Score, etc.. for 5 rounds.

I can get it to display the information, but it will not allow me to have an "updateable" recordset (whatever that means!).

Any suggestions??

Thanks
EIEIO

 

[kathryn]

OK, here I go again. I'm sorry to say that your table design really should be modified. Each table in a database should refer to only one "thing" and then you need to examine the relationships between the things.

In your case, you have:

Matches
Games
Players
Rounds
Scores

One Match can have many Games, and One Game will be associated with one Match. This is a one-to-many relationship between Match and Games

One Game will have Many Rounds, and One Round will be associated with one Game. This is a one-to-many relationship between Games and Rounds.

One Round will have Many Players, and One Player will play in Many Rounds. This is a many-to-many relationship between Rounds and Players

Finally, each distinct Round and Player can have a score associated with it.

The above all leads to your database having the following tables

tblMatch
--------
MatchID (autonumber Primary Key)
MatchDate
MatchLocation
etcetera...

tblGame
------
GameID (autonumber PK)
GameInfo????
MatchID (Foreign Key to tblMatch)

tblRound
--------
RoundID (autonumber PK)
RoundInfo????
GameID (Foreign key to tblGame)

tblPlayer
---------
PlayerID (autonumber PK)
PlayerFirstName
PlayerLastName


tblScore
--------
ScoreID (Autonumber PK)
RoundID (Foreign Key to tblRound)
PlayerID (Foreign Key to tblPlayer)
Score

I'm sure that this is a lot more than you bargained for, but I am a bear on database design, and your difficulty is due to the design, so I just couldn't help myself.

Hope this helps. Let me know if you need more info. ;-)

Kathryn

 

[eieio]

Thanks Kathyrn.

Yes that's a lot. My problem with designing the database the way you described is then being able to get all of the information that I need out of it. I had it structured similarly to that originally, but then couldn't figure out (and neither could the newsgroup guru's) how to get all of the different information out of it.

This is a huge database. It contains over 1000 PlayerMembers, 264 teams, oodles and oodles of matches and games.

For instance, everyone originally told me to "normalize" and not store the averages as a figure, but rather to have Access recalc them everytime, but I can't do that because each players average may, and usually does, change for each match, but there are times when we have to refer back to the average that a person used in a particualar match.

Secondly, we base their averages upon not only their score, but the scores of all of their opponentsas well (among other factors) - except games where certain criteria are met (This was a toughie for them to figure out how to do when it was a "normalized" database, and actually it never worked). Once we got it into the present form, then it was much easier.

There are over 70 reports that I pull weekly from this database and this was the only way that I could figure out how to get all of the information where we could actually EXTRACT it correctly later.

I would LOVE to have this thing normalized,but without paying a programmer a Gazillion dollars (approx.) I don't think I have the knowledge to set it up. After all, somewhere down the road someone told me Access was easy . . . . HAHHAHAHAHAHAH.

Oh well, just thought I'd explain a little further . . . (And, I will go back and try this out!)

 

[MichaelRed]

You REALY need to follow the path kathryn is showing you. A database of 1K players & a few hundred teams and "oodles" of details is not generally considered "huge" (or even large), however the complexity of severly de-normalizing the data will create much more confusion than the creation of the queries and relationships (to generate the reports).

If the 'newises' are not able to (or interested in) helping, you just need better (more interested) 'newsies'.

If the process is to complex for you in the 'normalized' Ms. Access format, then it is almost certain that it will be to complex for you in the severly de-normalized form described in your first post. If kathryn et al are not able to wend your way through the process, it may be worth the time / effort to review the technical literature available - either the tutorial supplied w/ Ms. Access or one of the plethora of third party books available to supplement the tutorial.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over