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

Kamis, 27 September 2012

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

Hi Bill,

I tried out the code with the single & double quotes.

Something seems to not work right.

Unable to figure it out.. Maybe is there a mistake in the DAO coding ?

Regards,

Madhu

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Wednesday, September 26, 2012 22:43
To: MS_Access_Professionals@yahoogroups.com
Subject: [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-6427073088
1E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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]
>

_____

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

_____

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2677 / Virus Database: 2591/5295 - Release Date: 09/27/12

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