Iif

[pl456]

I am struggling to get my head around this. I am trying to setup a football dream team db based on 3 tables of data, these tables will hold player data. table1 from start of season, table2 from 1st transfer upto 2nd trasnfer and table3 2nd transfer to season end.
My thinking at the moment is to query these tables and a 4th table of teams people have created. The teams table will have 3 yes/no fields
for activestart, active1st, active2nd to indicate at what point in the season the player begins to score points for the team.
However I am stuggling to come up with ways of returning the data needed and displaying this on a report

The query I have created at the moment returnes the score fields for each player from the data tables (data1, data2, data3)

I have put this expresison into the query

Expr1: IIf([activestart] And [active2nd]=True,[data1]+[data2],"")

Which works and returns the scores for the 2 active periods

I also have this expr setup for another combination of the 3 yes no fields.

Expr2: IIf([activestart] And [active1st] And [active2nd]=True,[data1]+[data2]+[data3],"")

Again this works,
I would then put all these Expr onto a report in thoer own controls and have a final control that adds them all together giving the total score for that player.
However this isnt going to work because some of these Expr will be true at the same time (the 2 I have given can be) thus double accounting.

Perhaps the idea is all worng. Does anyone have any suggestion? Please.

 

[namliam]

I would not make seperate tables for transfers, instead have 1 table and add "valid from" and "valid to" dates to determain if/when a player is elegable to score for the team.

That way you dont have to do anything with IIFs or what nots, you can simply "query" the active players (at the time) and sum the points using the query.

 

[redneckgeek]

This all depends on whether we're talking American Football or everybody else's Football.

 

[pl456]

We are talking proper football, with a correct shaped ball

The data is presented as just a total for each player on a web site, I can highlight and copy this data then import into access. The 3 tables would allow me to deduct the values from one another.