Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Updating one table when changes are made to another table in the same DB

    Hello All!

    I am a bit new to access so forgive me if this question has been asked and answered before. I have a database with multible tables and I am trying to set up the database so when I make changes in one table that access automatically updates another table. It is an inventory db so when I add or remove inventory from one table the database should modify the info in another field. Any suggestions????

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by tmhagans
    ...I have a database with multible tables and I am trying to set up the database so when I make changes in one table that access automatically updates another table. It is an inventory db so when I add or remove inventory from one table the database should modify the info in another field.
    I think you really need to do a little more explaining about your situation. It sounds an awful lot like you're storing the same information in more than one table, which violates normalization rules, which is to say the whole point in a relational database.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Quote Originally Posted by Missinglinq
    I think you really need to do a little more explaining about your situation. It sounds an awful lot like you're storing the same information in more than one table, which violates normalization rules, which is to say the whole point in a relational database.
    Well in one table I basically have the total number of supplies for a certain part (ie 17 cables) and in the other table we want to be able to input how much inventory we put in or take out. For example, if we use 2 cables the table with the inventory total of 17 needs to automatically change to 15. We want to input -2 (or +2 if we were adding parts) in one table (the input table) and automatically update the inventory totals in another table. Is this possible???

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, but you have to be VERY careful WHEN the update of the other table is done. Plan it well.

    You can modify the other table with a small VBA code segment. One of the tricks is to make sure that is only ever done once.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •