using values in one table as fields in another

[Matt Pearson]

I can't seem to work out how to do the above.

Basically what i want to do it have a table where dates
are stored as values, which are input by the user, this is
for poker competitions held at differing dates over the
course of a year

example

a competiton is held on 01/03/2004

the user inputs this data in to a table
called "Competitions" before the comps take place.

when the comp has taken place i need a new table "player
Stats" with "01/03/2004" and "player name" as a fields so
data can be stored on how much that player spent on that
date.

what i cant work out is how to desing a query that will
look at the date values in the "competiton" table and then
assign new fields for those dates in the "player stats"
table.

 

[Chris]

You're thinking about it the wrong way. You should do
something like:


tblEvents:
EventID AutoNumber Used as a
Primary Key
EventDate Date/Time Date Event was
held


tblPlayers:
PlayerID AutoNumber Used as Primary Key
PlayerName Text Player Name


tblParticipants:
EventID Number (Long) Links to
tblEvents.EventID
PlayerID Number(Long) Links to
tblPlayers.PlayerID
AmountSpent Currency Amount player
won/lost.



(EventID and PlayerId would be a composite PlayerID)


Now, if you want to find out standings, who won the
most/lost the most, you could:

Select PlayerName, Sum(AmountSpent) from tblPlayers INNER
JOIN tblParticipants on tblPlayers.PlayerID =
tblParticpants.PlayerID

To view the dates as fields, use a Crosstab query.



Chris