Linking Dbases

[Gretchen]

I have 3 databases, NAC, CCP, Master. These weren't created by me, I'm coming in behind and trying to make sense of this. The President of our company wants the NAC and CCP dbases to link to the Master for name, address, phone, fax, email and contact type. All other pertinent information will reside in the NAC or CCP dbase. There are no common fields in which I can create a relationship. CCP has auto as the primary key. Neither the Master nor the NAC dbases have a primary key. All 3 dbases each have approximately 2000 records - however most are not the same.

My quest is this:
1. Have all the records in the Master
2. If contact type is NAC, name/addy/etc appears in both Master
and NAC dbase (not in CCP)
3. If contact type is CCP, name/addy/etc appears in both Master
and CCP dbase (not in NAC)
4. Any contact type that is null appears only in Master dbase
5. Also, any correction to name/addy/etc can be made from any dbase and correct automatically all others.

Can anyone tell me how to do this?? I did this once at another job, however I created the master dbase and assigned permissions and users only saw what they needed to see on "their" dbases on their pc. There don't particularly need to be permissions; but if only way to do it, I can assign such.

Help! Every time I start to think about this, I feel almost overwhelmed. I'm not sure which direction to go, so if someone could gently start me out/point me in right direction, I'd be so very very grateful!!!!

 

[edtab]

You mentioned that you have three databases each containing tables with no common fields to create a relationship upon.

You may not be aware of it, but you can create relationships based on fields with different names. It is not necessary to have the field name to be the same for you to create a relationship.

I hope this helps you get started.

edtab

 

[Gretchen]

Yes, I thought so. However there is nothing in each that is a specific to that record. I thought about creating an id key for each and comparing the records, then assigning the id keys to be the same in each. However, does that link each database then? Does the information flow from one to the other and back again?

 

[ddmcn]

Gretchen,

When you say link...have you linked the tables in the Master db?

In other words, have you gone to:

File>Get External Data>Link Tables

to create the links in your Master DB?

 

[Gretchen]

hmmm guess I didn't explain myself well .. sorry. Linking them isn't the problem. Finding a field that is logical in each table is the problem. I think I'm just going to have to create an id field in the two tables that do not already have it and assign the numbers to like records so that each identical record has the same id number . You don't know how much I appreciate you all trying to help me with this mess!

 

[Gretchen]

Ok, I've created ID# field in each table and linked them so the relationships are set. Now, how do I find the duplicates in each table? Suzie Smith at 101 Main St in Duluth, WS may appear in the CCP and the NAC but not the Main table. How do I find where the duplicates are? (hope that was understandable)