auto update of two linked tables

[colenzo]

Hi guys,

I'm a bit stuck on a database I'm designing.

I have two tables (admin and warehouse) which in essence contain the same data (I know its easier to keep it to one table, but from an IT point of view, our warehouse need their data in a separate table), and I need to create a button on the entry forms that when clicked/entered will automatically update existing data in the other table.



Currently, I have an append query that takes the data being entered in the admin table and appends it to the warehouse table for viewing, however, if the data needs to be changed for some reason, and the button is clicked again, it does not update the data. I will also need to 'reverse' this code so that any data that is entered at the warehouse end, or any changes, will update the admin table

Does anyone know if this can be done in code, and if so how to go about it. I'm fairly new to databases and self taught so I'm a little hit and miss with my solutions and I'm running out of time before our company gets busy to sort this problem out. Thanks!

[RuralGuy]

Is the warehouse not able to access the same data? Is it a replicated db? I am curious as to why "...from an IT point of view, our warehouse need their data in a separate table."

[colenzo]

The warehouse has a tenious link to the main server, and cannot open tables with extensive data within. Whilst the admin table would hold all the data for enquiries purposes, the warehouse table would be exported out to archive on a daily basis. This is why the information will be duplicated. The admin and warehouse table will contain other data beyond what would be common between the tables.

I'm still trying to figure out a way of keeping the common data up-to-data between the tables if you know a way. I've so far been using append queries and primary/foreign key alignments, but it doesn't seem to be working as fully as I want it, and once data is entered, it won't update with an append query if the data is changed. It also needs to be two way. Help!

[colenzo]

Ok, a ran a modified update and append query and it now appears to be working... thanks for the help anyway!

[RuralGuy]

Excellent! Thanks for posting back with your success.