Results 1 to 11 of 11
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Updating data

    I have 200 students in tblstudent

    They are linked to other information such as assessments

    When I want to update my list of students (it changes once a week), I cannot just delete old and replace with new as the links with other tables prevent this.

    Is there a work around without removing referential integrity.



    Many thanks

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It really depends on what fields you are updating, but perhaps do the following:
    1. Import the records into a "temporary" table, which is really structured exactly the same as your Student table
    2. Run an Update Query to update existing records in your Student table from the information in your temporary table
    3. Run an Append Query to add new students from the temporary table to the Student table

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why would the studens change weekly? You don't delete students. You want to keep them and their work for history.
    you either add new students or reUse an existing.
    the assessment is new for that week.

    or backup that db w dates,then wipe all data.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Ok so test if a student is new then add them?

    And delete a student who has left?

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I agree with ranman. Deleting old records could really mess things up, especially if they are linked to records in other tables.
    I would just maybe have an "Inactive" flag field (Yes/No) that you could toggle if they are not found on the file (using an Update Query, once again).

    The process I usually use is create Select Queries to identify the records that I need to do action on, then change them to the appropriate action query.

    So, far changes, simply do a query with an Inner Join between to look for records that appear in both tables, and then change it to an Update Query and update the appropriate fields.
    For new adds, simply do an Unmatched query from your temporary table to the Student table, and then change it to an Append Query and update the appropriate fields.
    For marking "Inactives", simply do an Unmatched query from your Student table to your temporary table, and then change it to an Update Query updating the Inactive flag.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Using an active inactive field is a great idea.

    Thanks for the advice

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I prefer using dates, better traceability. DateInactive.

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks aytee. The only issue for me I think is I have a table which now has studentactive.

    Do I rewrite every query so only active students are listed?

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    One way is to make a query that just returns the Active Students.
    Then you can replace the queries that use the "studentactive" table to use the new query instead.

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Sounds easier. Thanks Joe.

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

Similar Threads

  1. Issue updating data
    By ryanmce92 in forum Forms
    Replies: 4
    Last Post: 06-23-2015, 05:31 AM
  2. updating data
    By Sarge, USMC in forum Access
    Replies: 1
    Last Post: 01-05-2013, 09:11 PM
  3. Updating Data in a Form
    By asmith533 in forum Forms
    Replies: 1
    Last Post: 09-11-2012, 10:45 AM
  4. updating a form with new data
    By markjkubicki in forum Forms
    Replies: 6
    Last Post: 08-25-2010, 01:29 PM
  5. Importing and updating data.
    By mikej2009 in forum Import/Export Data
    Replies: 1
    Last Post: 05-23-2010, 11:30 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