Results 1 to 4 of 4
  1. #1
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107

    How to update other tables from a master table


    Hi there,

    I have a database with 7 related tables. Now the main table is called STUDENT. I am essentially creating a report card system and have broken down each part of the report into another table. Now to make it easy for teachers I have created a form which they can search for a class and in a datasheet all the students plus their ID numbers come up along with the spaces for them to enter their grades etc.

    The problem I have is this. How can I get access to look for any updates from the STUDENT table and apply this to the other tables as it would not do this automatically, even though they are linked one-many I have copied the data from the main STUDENT table into each other table just so theres a reference for a teacher. Is there any magic button or something that I can make to do this so all extra student names and IDs are added to all other tables?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Should not be adding student names to other tables, only student ID. What do you mean by 'updates' - new students? There is no magic, only code and yes you can make it.

    You might find this of interest https://www.accessforums.net/access/...ase-23168.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aussie92's Avatar
    aussie92 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2013
    Posts
    18
    I am not sure if what I am saying will solve your problem, so please forgive me if anything I say is insulting to your understanding of Access...

    Basically, if you have a "Master" table, there is no need to have the same data that table holds in other tables. The way you would get the teachers to see everything would be to create a query that links based on a student ID or a FirstName.LastName type of "key identifier." That way, all the "heading" data for a student like GradeLevel, HomeRoom, PhoneNumber,BusRoute, or whatever else you need, can be kept in the "Master" table, STUDENT. Each record in the STUDENT table should have a key identifier, lets say, called "StudentID." This is a non-repeatable, non-duplicable number or number/text combo for each student. Lets say John Doe is student number s0001527b. When a Math Teacher needs to enter Johns test scores, she would open the MATH table and enter John's student id number (s0001527b) and whatever else is needed for that table (test score, test number, comments on test, etc.). Then when the Principle needs to see all of Johns MATH and HISTORY scores, they could go to a query which queries off three tables (STUDENT, MATH, and HISTORY) and links by the studentID number. There, the principle would be able to see all of Johns scores, his home room, grade level, and other "header level" info that is only viewable from the STUDENT table.

    Does that help at all?

  4. #4
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    Oh this makes sense. I know it goes against database design but I just wanted to ensure a teacher could see everything but silly me didn't think about doing this via a query that can sort all this business out. Thanks and sorry for being so silly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Master Table with Temporary Table
    By kagoodwin13 in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 11:59 AM
  2. Replies: 5
    Last Post: 08-22-2013, 04:32 PM
  3. Replies: 1
    Last Post: 08-19-2013, 10:59 AM
  4. Relating Multipe Tables With Similar Data To A Master Table
    By TotalChaos in forum Database Design
    Replies: 1
    Last Post: 04-13-2013, 12:57 AM
  5. Replies: 1
    Last Post: 07-27-2010, 08:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums