You definitely have some "grunt work" ahead of you to clean up discrepancies in your Company name data. There'll be no substitute for using a human brain to analyze and resolve many of them. You may find it easier and faster, however, if you set up a table view of your data and sort it by company name to try to pull similar company names together so that they are adjacent to one another. You may also find it useful to perform searches with wildcards to find a group of records that have slightly different spellings of the same company name.
Your other questions clearly indicate why you need to upgrade to a set of related tables. If you can structure your relationships properly, most of these questions should be answered in the process of setting those up.
would it then be better to have Addresses linked to Companies or to Contacts?
You can do both. It doesn't have to be an either-or decision. You can link the same Address record to multiple contacts and to multiple companies. Sometimes it's useful to treat companies and people as two different types of contact record in the same table and other times it makes more sense to have two different tables, but either way they can be linked to the same table of address records.
With your other two questions (Multiple addresses for the same contact or multiple ship to addresses for the same company), you can use a portal to display as many related address records as you need.
The grunt work is what I was not looking forward to. The other parts are much more fun.
So for each table I plan on a primary key, ContactID, CompanyID, etc
So if Jim Smith is a consultant that works for 2 companies I would enter him twice, once with each company. That way he would have 2 contactID's with each related to the address for the appropiate company if he had officecs at each one, or both would be the same if he worked out of his own.
There's no need to enter "Jim Smith" twice. You can link the same contact record to more than one company. Also consider that if you give Address records an AddressID, you can reverse the linkage to link a single address to more than one company or contact record.
The trick is often to use a join table so that you can link one address record to many contact records and one contact record to many addresses:
Contact::ContactID = Contact_Address::ContactID
Address::AddressID = Contact_Address::AddressID
With such a relationship, you can place a portal to Contact_Address on a contact layout to list multiple addresses and a portal to Contact_Address on an Address layout to list multiple contacts with that address.
Ok. So it would be possible to have a layout where Jim Smith shows up at his Boeing addresss when the company is Boeing and Northrop when the company is Northrop?
It's possible. If you are using FileMaker 11, one method could be a filtered portal. Other options depend on how you structure your relationships.
I am using FM 11 on a mostly Mac network. There may be more and more PCs down the road if I can put together an accounting package someday.
As much as I've learned so far, that task seems pretty daunting still.
I think I understand how using a join table with those relationships should be able to handle the one contact/multiple company situations. And having the addressID in both the Contact and Company tables should allow you to enter a new contact name - select a company - then select an address from addresses related to that company or enter a new one if needed. The address with then be tied to the company and to that contact. But no that won't work since that company record will already have an addressID from a previous address.........I don't know how that would work.
It would work the same as I illustrated with contacts and addresses:
Companies::CompanyID = Company_Address::CompanyID
Addresses::CompanyID = Company_Address::CompanyID
This is one of the reasons I suggested you consider whether companies might be treated like just another kind of contact as this can simplify your relationships. (And you can still link contacts to companies even when both records are in the same table.)
OK, just took me a minute (and a heavy blunt object) to wrap my head around that join table and applying it there as well.
So there would be another table(s) with just the ID numbers, which I could use to bridge a company to a contact to an address?
Or even possibly a different occurrence of the same "join" table or your contacts table and your company table might even be the same table.
So would it be easier to keep the company name field in the Contacts table and not have a Companies table? And just have Contacts who have Addresses and Phonenumbers.
This isn't a question that has an easy answer, you'll have to weigh the trade off's and decide what works best for you and your database.
What you describe may work for you.
Another option is to consider a company to be one kind of contact, but structure your database so that yet another join table allows you to link contacts to each other. That would allow several "person" contacts to be linked to a common company (or more than one company) and a Company contact can list several "person" contacts.
And Companies can be a completely separate table. Take a look at what other data you need to record about a given company. If it's all just the same contact information as you record for a person, then one of the upper two options is likely all you need. If you have a lot of other data that you record just for companies and not for persons, then a separate table makes sense so that you don't have to record the same data over and over again for each contact from the same company. Keep in mind too that many companies have a phone number that is not specific to any one individual but reaches a receptionist instead. Likewise, I would think that a given company or department within a company could have several contacts but just one billing and/or shipping address.
A first run through extracting companies and elimation of duplicates gets the number of companies down to 350, the number of contacts is ~4400.
Most companies have 1 billing address and sometimes more than 1 shipping address. Some contacts have the same address as others at the same company but a specified "mail stop".
It was making sense to me to have a separate company table, then associate addresses to contacts and at least one main address to the company.