Re: Synchronize between two tables in access

Giganews Newsgroups
Subject: Re: Synchronize between two tables in access
Posted by:  Smartin (smartin1…@yahoo.com)
Date: Thu, 03 Aug 2006

pradeep wrote:
> Hi,
>
> I amnew to this group and lucky to have found this group. i have a
> master table which has different types of application say Desktop,
> Mainframes, etc.
>
> I have an individual table for each of these application types.... one
> for Desktop, one for mainframes, etc
>
> How do i synchrinize data between these two tables, say i update the
> Mainframe application description in Master table, it should get
> updated in the Mainframe individual table.
>
> TIA,
> Pradeep

So, in the individual tables, what do the columns look like? Is it one
long list of applications that fit the description (table name?)? or do
you have one column with the description, and another for the
application name?

If either of these guesses is the case, you are trying to store similar
information in separate tables, which is a no-no in database design and
makes your request tedious (at best) and error prone.

A much better way to store this information is to store application
names in a single table using a /reference/ to the types table. The
types table should have a unique key that cannot be changed, and a
description. The application table should have a unique key, a foreign
key to the types table, and a description.

So your table definitions should look something like this:

table ApplicationTypes
======================
ID (Autonumber) (Primary Key)
TypeDescription (Text)

table Applications
==================
ID (Autonumber) (Primary Key)
TypeID (Foreign Key to ApplicationTypes.ID)
ApplicationDescription (Text)

Now, regardless of the description in ApplicationTypes, the following
query will always return the correct association of applications and
types, as long as TypeID points to the correct ApplicationType.ID:

SELECT A.ApplicationDescription, T.TypeDescription
FROM Applications AS A LEFT JOIN ApplicationTypes AS T
ON A.TypeID = T.ID;

--
Smartin

Replies

None

In response to

Synchronize between two tables in access posted by pradeep on 3 Aug 2006