Automatically updating Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm trying to set up a database and I need some help. I need to set up
two tables so that when information is entered into one table (call it Table
A) it automatically updates into the other (Table B) but not vice versa.
Basically, Table B is a Master list and Table A is a current one, which will
change periodically. Is it possible? Help would be appreciated. Thanks.
 
You could create another Table making sure that the relevant Field names are
the same then using Link join the maindata Table to the new one and as you
update the main one the data in the matching Field names will transfer
across.

Bruce
 
Hi, I'm trying to set up a database and I need some help. I need to set up
two tables so that when information is entered into one table (call it Table
A) it automatically updates into the other (Table B) but not vice versa.
Basically, Table B is a Master list and Table A is a current one, which will
change periodically. Is it possible? Help would be appreciated. Thanks.
Click to expand...

It sounds as though your database design is faulty.
There should be no reason to store the same data in 2 different
tables.
In one main table, include a field that indicates whether the record
is current or not (it could be a check box defaulting to Current).
When the record is no longer current, uncheck the checkbox field,
manually or using code, or if you have some other criteria, by
referring to that criteria.
Using a query to filter records, you can then display the current or
the complete set of records.
 
Thanks for the advice, but having a "current" field won't work. Basically, I
run an annual convention and the way I want it set up is one table will have
the Event information for the current year and the other table will hold the
Event information for all the years (so we have a historical record of the
different events we run and which year they ran). What I would like is when
I put an Event in the Events 2005 Table, that record is automatically also
put in the Events All Table.

One of the reasons I would like it set up that way is that I am also using
this database to take care of registration in our events. I want to set up
our Registration Form so that the Event fields are drop-down lists from the
current year's event listing.
 
Hi Heather,

It's your application and you can do what you want but there just is no good
reason to separate the tables by year. If you simply have a date field in
the relevant records then you've got the separation by year issue under
control. Entering the same data in two places in the database breaks one of
the relational rules. It is altogether too easy for data to get out of
synch. Then which data is to be trusted once you know the tables differ?
If you have your application split into front end and back end then the back
end can grow to about 2G before you hit the size limit. Admittedly, you'll
probably have performance issues before that. Typically when the database
needs paring you'd copy the database off to a CD or some other very secure
place and then purge the older records out of your current database. That
works nicely if you have Referential integrity enforced and allow Cascading
Deletes.

All of the issues you mentioned can be handled with the inclusion of a date
field in the main table.

HTH
 
Back
Top