On Thu, 05 Feb 2004 03:57:38 GMT, "Steve" <sa****@penn.com> wrote:
John,
Thanks for responding!
My example over-simplified the problem. Suppose a person is in 4 sources. The
record I want is:
FName, LName, SSN, MembershipNum, SchoolDistrict, Position, City, State, Zip
Ok, let's call this MasterTable... what's its Primary Key?
MembershipNum? If it has none... it's not a table, it's a random
collection of data which cannot be updated.
The available data in the sources are:
#1: FName, LName, SSN, City, State, Zip
#2: FName, LName, MembershipNum, City, State, Zip
#3: FName, LName, SchoolDistrict, City, State, Zip
#4: FName, LName, Position, City, State, Zip
Ok. Some painful questions here:
Table1 has a record for Fred Smith, SSN 445-44-4444, in Poughkeepsie;
and also a record for Fred Smith, SSN 454-44-4444, also in
Poughkeepsie.
Are they the same person? Is either one the same person as the Fred
Smith, SSN unknown, in Schenectady in the master table? If so, which
one? Or maybe it's the Frederick Smythe, SSN unknown, in Poughkeepsie
You MUST - NO OPTION!!! - have some way of uniquely identifying which
record is which. Names *are not such a method*. They're not stable,
they're not unique, and they're not reliable.
ASSUMING... and it's a heck of a big assumption - that the names are
reliable in this case, what do you want to do if Table1 has one
city/state/zip and Table2 a different City/State/Zip, and MasterTable
hase these blank? Maybe the person moved; which address should Access
use?
ASSUMING... even bigger assumption... that every record has FName,
LName, City, State and ZIP in all five tables, reliably and
accurately, you can construct five update queries. Create a unique
Index on these five fields in MasterTable. Create a Query joining
MasterTable to Table1 on the five fields and update MasterTable.SSN to
[Table1].[SSN]
Run the query by clicking the ! icon.
If you'll be doing this kind of exercise in the future, you may want
to look into a technique called "Replication". It's not for the faint
of heart, but it's designed so that you can make five replicas of a
database on five laptops, have people indpendently update all five,
and synchronize the data back into one table. See
http://www.trigeminal.com and search for Michka's Replication FAQ
there, it's the definitive word on the subject.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public