ACCESS FEED: [MS_AccessPros] reg: Calculation of Sum from one table to another

Rabu, 26 September 2012

[MS_AccessPros] reg: Calculation of Sum from one table to another

Hi,

I am having a query regarding totaling from one table to another.

The following are the details of my tables.

TABLE NAME

STUDENT_MASTER

STUDENT_DETAIL

FIELDS

STU_CODE

STU_NAME

STU_TOTAL

DET_CODE

DET_MARKS

The stu_code of student_master is linked to the det_code of student_detail
table.

The issue is, am unable to calculate the sum of det_marks and add up for
each student name in student master.

I have tried using the DAO, using the following code. But somehow it does
not seem to work.

Private Sub Command0_Click()

Dim rsname As DAO.Recordset

Dim marks As Double

Dim stucode As String

Set rsname = CurrentDb.OpenRecordset("Select * from student_detail")

rsname.MoveFirst

Do Until rsname.EOF

Set stuname = rsname!stu_code

CurrentDb.OpenRecordset ("Select sum(det_marks) as marks from student_detail
where det_code = stuname")

CurrentProject.Connection.Execute "update student_master set [stu_total] =
marks"

rsname.MoveNext

Loop

Set rsname = Nothing

End Sub

Madhu

_____

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2441/5293 - Release Date: 09/26/12

[Non-text portions of this message have been removed]