query the same table more than once

[eieio]

I have a table which contains the following fields:

Match, Team, Player, Game, Score


Because in every match a player will play up to five different games, I need
my form to be in the following format:

Match, Team, Player, Score, Score, Score, Score, Score

The "Game" fields fill in automatically with a macro and are not visible on
the form.

My problem is creating a query that will allow me to use this format,
without having to use 4 different subforms, because there are up to 10
different players per match and that would exceed the number of subforms
which should be used in Access.

The problem is that each "score" must be its own record because of some of
the calculations that are used later.

I have tried making a query that has Match, Team, Player, Score and added
the score field 4 more times, but this time of query is "Not Updateable".

ANY ideas are invited. Please help.

 

[Nebuchednezzar]

add a column SCORE_NO and have it increment from 1 to ... for each record that is a duplicate of match, team, and player.

This way, instead of having multiple columns for the score in each match for a player, you will have multiple rows.

When you query this way, you can group by match, team, player, and sum the score (or do whatever it is you're going to do).

Kind of hard to tell exactly what you are doing with it (what kind of game are you keeping score for?)

 

[MichaelRed]

I more or less agree w/ "Nebuchednezzar". Not quite enough info/description, .... BNUT .... it sounds a lot like a crosstab query from here.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[eieio]

Well, I'm not sure that I understand. In my table I have multiple rows not columns. Is that what you mean?

I use this to keep score of league matches (pool). In every match, there are two teams. For each team you have up to seven players and each of these players may play up to 5 games. However, the statistics are based on each Players results, so I need to have a record for each game that every player plays.

I'm not sure what you are talking about when you say to add "score_no" and have it increment. Would I have to do this for EACH player in each match??? There are over 2000 matches and over 50,000 games! How would I get this to reflect in my form?

If you need more info, I can supply it. . . . thanks for all help.