use other table to calculate values

[kmo]

Dear all,

I have two tables

1st table contains
Table: Student
student (string)
Soccer (bool)
Soccer Points (number)
Basketball (bool)
Basketball Points (number)
Total Points (number)

2nd tables contains some constants like
Table: Activity
activity (string)
Points (numbers)

so in the second table one enters the activity and the point awarded to that activity like

Table: Activity
activity points
soccer 25
basketball 24

Now I use the points for each activity in the activity to calculate the points in Student table so

if the student plays soccer, the soccer points field should get the points as mentioned in Activity table.

Any ideas on how I can implement that? I guess I have to do this using VB, thats why I posted the question here. Thank you very much for the help.

 

[RV]

Now I use the points for each activity in the activity to calculate the points in Student table

That's a NO NO in database land
Use a query to calculate data.
Remove the Totals columns for your student table.

RV

 

[kmo]

I mean calculating the total points is ok but to assign the student.soccer points value from the activity.soccer is more pain.

Sorry I forgot to mention that I have a form to display the table values. So when in the student form, soccer is checked as yes, the soccer points should automatically appear as given in the Activity table.

I tried this but with no success ..
//stuActivity is the actvity from the student table eg. soccer
function Getpoint(stuActivity As string) As Integer
Dim Points as Integer

// using sql i tried this

Points = Docmd Runsql "SELECT Points FROM Activity WHERE Activity=stuActivity"

Getpoints = points
End function

..I know I have lot of mistakes here but the idea is to get the point value from the sql query but i dnt know how to store the result of the sql query in a variable. Its also guaranteed that the query should give only one column as a result!

I am little new to vb and sql both so hope to learn mnore here !!

 

[kmo]

any help !!?? i am quite urgently in need of this !!!

 

[goppo]

try this.
I dont know if sintax is ok...

Dim rcsmdb As DAO.Recordset
dim GetPoints as Integer
Set rcsmdb =CurrentDb.OpenRecordset("SELECT Sum(Activity.Points) AS TTLPoins FROM Activity where Activity like '' & stuActivity & '';")
Do While Not rcsmdb.EOF
With rcsmdb
Points = !TTLPoins
End With
Loop