Calculations when using look up tables

[Chris_A1]

I have created a form where the first three fields are the customer's details and the next eleven are the products they've chosen from a look up table.

My problem is that I then want to make a "Total" field based on the total value of the products they've chosen.

I've tried this formula...

=sum((Gk1.column(2))+(Def1.column(2))+(Def2.column(2))+(Def3.column(2))+(Def4.column(2))+(Mid1.column(2))+(Mid2.column(2))+(Mid3.column(2))+(Mid4.column(2))+(Str1.column(2))+(Str2.column(2)))

...but it just returns an error.

When I include the formula but without the sum function it lists all the values so i've done something right.

If anyone can help with this I would really appreciate it.

Thanks.

 

[Jack Cowley]

If you use = Gk1.column(2)+Def1.column(2)+Def2.column(2)+Def3.column(2)+Def4.column(2)+Mid1.column(2)+Mid2.column(2)+Mid3.column(2)+Mid4.column(2)+Str1.column(2)+Str2.column(2) do you get an error? If so, what is the error? Are any of the values Null values?

Are the eleven fields combo boxes? Why are you doing it this way? Can you not use a form/subform and display the customers orders (or whatever) in the subform and total it there? I'm not sure what you are trying to do but it seems from what little you have said that maybe you are going about this the hard way.

Jack

 

[Chris_A1]

I'm trying to create a form for people to fill in for a fantasy football league.

The eleven fields are the eleven players they have to choose from a list of first goalkeepers, then defenders, then midfielders, and then strikers.

I then need the total field at the bottom of the form to tell them the total value of the players they have chosen (this value can not be above 35 so I will have to set a validation rule for this somehow too).

I've tried the formula you suggested. This does not return an error but just lists the 'values' instead of adding them up.

I think I am maybe going about things the hard way but its the only way I know how!

 

[Jack Cowley]

The eleven 'players' should be in a separate 'lookup' table that you can use to populate a subform for every person who joins the 'league'. You will then have a form/subform where the main form has the person in the league and the subform his/her 11 choices. They tick off their choices in the subform and if their total is above 35 you can catch it there. Your tables might look something like this:

tblMembers
MemberID (Primary Key and autonumber)
Surname
FirstName
...other fields...

tblPlayersLookup
PlayersID (Primary Key and autonumber)
MemberID (Foreign Key and long integer)
PlayerPosition
PlayerValue
Selected (Yes/No field that member ticks if they want this player)

Now this may not be correct as I do not know how you have Teams set up, etc., but maybe this will give you an idea on how you can approach your database. If you have more question just post them and I am sure that someone will be here that can help you.

Jack

 

[Chris_A1]

Right, thanks for your help.

I'll try out your suggestions and post a reply with how I get on tomorrow.

Thanks again.

Chris