Automatically updating Tables

23 Feb 2005 8:13 PM

Heather

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.

23 Feb 2005 8:32 PM

Bruce

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
Show quote

"Heather" <Heat***@discussions.microsoft.com> wrote in message
news:D9F8ED3F-5A51-4C18-B901-8301D2165A29@microsoft.com...
> 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.

23 Feb 2005 8:34 PM

fredg

On Wed, 23 Feb 2005 12:13:09 -0800, Heather wrote:

> 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.

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.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

23 Feb 2005 8:49 PM

Heather

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.

Show quote

"fredg" wrote:

> On Wed, 23 Feb 2005 12:13:09 -0800, Heather wrote:
>
> > 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.
>
> 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.
> --
> Fred
> Please only reply to this newsgroup.
> I do not reply to personal email.
>

23 Feb 2005 9:16 PM

Larry Daugherty

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

--
-Larry-
--

Show quote

"Heather" <Heat***@discussions.microsoft.com> wrote in message
news:1A07F869-B238-41FB-9525-00515A6A14DD@microsoft.com...
> 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.
>
> "fredg" wrote:
>
> > On Wed, 23 Feb 2005 12:13:09 -0800, Heather wrote:
> >
> > > 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.
> >
> > 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.
> > --
> > Fred
> > Please only reply to this newsgroup.
> > I do not reply to personal email.
> >