how can you automatically update a field in the table using a prior field entry?

[nitinrao]

Hi everyone

I'm trying to make a database for keeping track of my company's keys. I have three tables, as you can see from the attachment. I'm trying to make it so that the subdataform in "Key" shows the "Key Assignments" table while also showing the employee's last name. I have a combo box set up in a form for the "Key Assignments" table which lets you choose the employee's name from the drop-down menu and records it in the Employee ID field. But is there a way to automatically update the Last name field in the Key Assignments using this, already entered, employee ID earlier in the form?

I'm also trying to add the Building and Room fields from "Key" to "Key Assignments" for the same reason as I want to add the last name field - so automatically updating these fields using a prior entry of the foreign key will make data entry less time consuming when entering over a 1000 keys.

These are my two main goals for this database: 1) clicking on the expand button in the "Key Users" to see the keys they have (my supervisor wants to see them on the table instead of a query because she has some knowledge with access and this is quicker). 2) clicking on the expand button in the "Key" to see all the employees that possess the particular key.

Please help!

 

[stopher]

Hi

What you are proposing here is storing redundant data i.e. storing the same data in more than one place. This really goes against the grain of good (normalised) database design and is really unnecessary. I urge you not to go down this route.

Speed is also unlikely to be an issue until your database gets very large (well into the hundreds of thousands of records). Databases are specifically designed to make use of queries for referencing related data. So providing your tables are properly designed and indexed then you should have no problems retrieving all the data you need when you need it using queries.

Chris