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

Rabu, 26 September 2012

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

Madhu

I'm not promising this will work, but your syntax is incorrect. Whenever you use a variable in VBA you have to do it this way (I've broken up the lines to make it easier to read here):
CurrentDb.OpenRecordset ("Select sum(det_marks) as marks " _
& "from student_detail " _
& "where det_code = " & Chr(34) & stuname & Chr(34))

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

Note the student name is surrounded by Chr(34). That is the ANSI for a double quote. All text must be surrounded by either single quotes or double quotes. Doubles will allow apostrophes within a name like Mike O'Brien to go through.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, "Challa Madhukar" <challamadhukar@...> wrote:
>
>
>
> 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]
>