Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 28th, 2008, 07:00 AM
Registered User
Join Date: Nov 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default auto update from 1 table to column in another tabl


„X In 1 Academic Session, e.g. 2008/2009 Academic Session, there are 3 terms, which are 1st, 2nd, 3rd term
„X In each of the Term are 10 Classes which are Primary 1, 2 etc
„X Each of the Class has various number of Students
„X Each Student offer about 15 subjects like mathematics, English etc
„X Each Student write 3 Tests and 1 Examination Each Term, making it 9 Tests and 3 Exams in 1 Academic Session.

See my Table structure

Table 1-Academic Session
        Session Id (pk)

Table 2- Term
         Term Number (pk)
         Term Id(pk)
         Session Id (fk)

Table 3- Class
         Class Number (pk)
         Class Id(pk)
         Term Id (fk)

Table 4-Pupil
        Pupil Number(pk)
        Pupil Id (pk)
        Pupil Name
    Class Id(fk)

Table 5-Academic Records Table 6- Subjects
        Record Id (pk) Subject Id (pk)
    Pupil Id (fk) Subject Name
    Subject Id (fk)
    Test 1
    Test 2
    Test 3
    Total Test Score
    Exam Score
    End Of Term Score
    Last Term Cumulative Brought Forward
    Cumulative Score

Table 1 1-m Table 2
Table 2 1-m Table 3
Table 3 1-m Table 4
Table 4 1-m Table 5
Table 6 1-m Table 5

Academic expressions on Calculated Controls
Inside Academic Records Form Columns with numeric data types
See the Academic Records Table Structure Above
Total Test Score=Test 1+ Test 2+Test 3
Term Score=Total Test Score + Exam Score
Cumulative Score=Term Score + Last Term Cumulative Brought Forward / 2

My Headaches

This is what I want to achieve
When ¡§Term¡¨ = 1st
Let ¡§Last Term Cumulative Brought Forward = 0
When ¡§Term¡¨ = 2nd
Let ¡§Last Term Cumulative Brought Forward = 1st Term. Cumulative Score
When ¡§Term¡¨ = 3rd
Let ¡§Last Term Cumulative Brougth Forward = 2nd Term. Cumulative score

Do I need to create separate Tables for each Term? If I need to, How do I relate the Tables How do I program and execute it. Can my existing Tables designs work? Please help me


Old December 1st, 2008, 08:39 AM
Authorized User
Join Date: Jan 2006
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts

hi olaoul,
u will have to resdisgn your academic records table to include 3 new fields.
1.session id(fK) 2. term id(fk).3.uniqueterm(text fld)this field will concatenate sessionid and pupilid in academic table e:g 2007/2008 1245 or 2007/2008 1245.i:e assuming the student id is 1245.

    at form level,do the following
the fields created in the table level,will be added in the form level.
2.on the uniqueterm field on the form;change it 2 a combo box whose record source will be as follows
select from academic
uniqueterm, sum(cummulativescore)

on the row count property,change it to 2.

3.on the after update event of the uniqueterm,enter the following code
  Cumulative Score=uniqueterm.column(1)

4.select from the uniqueterm field the value that matches the studentid and the session id e:g 2007/2008 1245 assuming the pupil id is 1245 and the session is 2007/2008.this will automaticatically place the value in the sum(cummulativescore)in the row source property of the uniqueterm inton the cummulative score field.

to concatenate the pupilid and sessionid check concatenating fields in the ms access help file.

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to reorder the auto number column? al-hijjawi SQL Server 2000 3 April 28th, 2006 07:37 PM
How can I update 2 rows in one column in SQL table babou Classic ASP Databases 6 March 9th, 2005 11:13 AM
how to make column of table 1 = to column of table gilgalbiblewheel Classic ASP Databases 4 October 11th, 2004 11:57 PM
Auto OK on UPDATE Dialog Cr0vaX Access VBA 3 June 24th, 2004 01:24 AM
auto update table field nkrukovsky Access VBA 2 November 4th, 2003 05:28 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.