using values in one table as fields in another

  • Thread starter Thread starter Matt Pearson
  • Start date Start date
M

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.
 
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
 
Back
Top