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

Jumat, 28 September 2012

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

Let's just forget for a moment that storing a total is a bad idea. Hopefully you have a good reason for this. You should be able to create a simple update query to run.

Assuming STU_CODE is numeric:
UPDATE STUDENT_MASTER
SET STU_TOTAL = DSum("DT_MARKS","STUDENT_DETAIL","DET_CODE =" & STU_CODE )

Assuming STU_CODE is text:
UPDATE STUDENT_MASTER
SET STU_TOTAL = DSum("DT_MARKS","STUDENT_DETAIL","DET_CODE =""" & STU_CODE & """")

Duane Hookom
MS Access MVP

> To: MS_Access_Professionals@yahoogroups.com
> From: challamadhukar@yahoo.co.uk
> Date: Fri, 28 Sep 2012 12:08:38 +0530
> Subject: [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]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

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