Hi All

I wonder if anyone can help me I am trying to sum three fields from the below table in a query.

My table contains:

BowlerScoreID (PK)
BowlerID (FK)
Game1
Game2
Game3
Date

In theory this is storing 1 persons three games, so the value in game1 may be 150, the value in game2 may be 180 and the value in game3 may be 200. The total series would be 530.

What I want to do is calculate the sum of game1, game2 and game3 for each record and I thought that i would have to do this in a query as calculated fields should not be stored in tables?

What I also would like to be able to do is find out if any of the three games are above 175 and then store this is the query i think that I need to create a 'find duplicate query' have a field tell me how many of the games are over 175.

When i try to sum game1, game2 and game3 an error occurs.

In the next blank field in the query by example I have built this expression: Expr1: Sum([Game1]+[Game2]+[Game3]) and the error is:

You tried to execute a query that does not include the specified expression "BowlerScoreID" as part of an aggregate function.

Can anyone please help me. Thanks in advance

Kind Regards

Peter

You're table structure is not normalised (set out correctly).. You have repeating groups.

What happens when a person has 4 games....

Your table structure for the games should look like..

BowlerScoreID (PK)
BowlerID (FK)
GameNumber
GameScore

This way you could have if required as many games as you need

OK Thanks for that. I'm new to this. A bowler will never ever bowl more than 3 games in a match, a bit like football, 1 match gives a end result. In bowling three games make up a match?

Should I still implement your idea?

Kind Regards

Peter

Try

Expr1: [Game1]+[Game2]+[Game3]

It's up to you really... will there ever be a case where game3 isn't filled in (i.e. if they get injured in game2)?

In that case you would have a wasted field which you wouldn't have in my method... if something like that would only happen once in a blue moon then really you dont need to worry about it....

If it is possible that no score is achieved in one or more of the games then you need to change Ian's suggestion to

TotalScore: nz([game1],0) + nz([game2],0) + nz([game3],0)

Just to add... if you used my method the expression you would need is..

TotalScore: sum(nz([gamescore],0))

OK guys. thanks for your input. Much appreciated. Because all three games will always have a score, if a bowler was injured in game 2, then a blind score would be used. (Their average - 10), for the next game. You would never have a 0 score but I do take on board your idea and see the value aswell. But for the purpose of this exercise I will keep it as is and use one of the expressions above to acheive the sum.

Thanks again people.

Kind Regards

Peter

glad to help