3 Replies Latest reply on Jan 10, 2014 4:51 PM by philmodjunk

    Change all related records

    paulbrock

      Title

      Change all related records

      Post

           Hello! I am newly converted from the ms access world. I have put together a database to house all our inventory at our business. I have setup a table to store the inventory as well as a table that holds our employees. My end goal was to have the inventory entered as well as the current user of that inventory. So far everything is working great, but if I were to go into my employee table and change a user, someone left and a new hire was put in their place, it does not change the existing records in the inventory's employee field. The current relationship between the two tables is Inventory:User = Employees:Employee. I am still very new to creating databases, I only created one with ms access and it was a pain. I appreciate any help you can provide. Thank you!

        • 1. Re: Change all related records
          philmodjunk

               it does not change the existing records in the inventory's employee field.

               And it shouldn't change the existing records. Why do you need it to? Why does adding a new employee record require such an update?

               And it is better to link records by ID number than name. Employees can have the same name, they change their names and names are easily entered incorrectly. All of those issues can require needed to change employee names in related tables if you match records by name. But they are avoided if you use an ID number instead.

          • 2. Re: Change all related records
            paulbrock

                 Thank you for your reply.  I agree with your statement about using the ID. I had tried to do that but I was having a difficult time getting the drop down menu in my layout to display the names of the employees, but I have resolved that issue and now it is linked by ID not name. 

                 

            Why does adding a new employee record require such an update?

            I am not adding a record, but changing one. For example I have a manager that leaves and I replace him with someone new. I would like the inventory that the new manager is using to be linked to him/her and not the old employee. I have other people that maintain the inventory and use this database to do so. If they were not here the same time as the old employee they may not know where to find the piece of equipment and waste a lot of time looking, whereas if they see the new manager they know right where to go. I thought that changing the link to an ID not a name would fix it but it did not.

            • 3. Re: Change all related records
              philmodjunk
                   

              I am not adding a record, but changing one.

              Surely you are adding a new record in the employee table for the new employee? If, instead, you were updating the existing employee record to have the new employee's data, there should be nothing to update except for updating the fields in the employee record.

              But I would create that new employee record and then the existing records in inventory would need to be linked to this new employee record:  From the employee table, Go to Related Records is a script step that can be used to produce a found set of related inventory records on a layout based on the inventory table. Replace Field contents can then be used to update the match field to the value of the new employee record's ID.

                   There are also ways to set up your database where your inventory matches to a record for a specific position, which in turn is linked to a specific employee. In such cases, you could just update a single position record to link to the new employee record.