Question about data architecture
I have a problem that I'm worried goes beyond a forum post, but I figured I'd start here anyway, because I could be (and am hoping to be) wrong:
In my database I have three main tables: Firms, Contacts, and Meetings. Currently, the field CompanyID in the Firms field (the result of a helpful suggestion from this forum) is the key linking factor between the three. Any contact records must be assigned to a firm via a CompanyID, and any meetings must also be assigned a CompanyID so that we can know which firms our clients are meeting with.
The problem is that I think this prevents me from being able to single out specific contacts for some relationships in my database. Here's how it currently looks:
Firms Contacts Meetings Meetings 2
About 90% of the time, this is fine, and going to related records and moving between layouts works perfectly. However, there are times when I ask the database to pull supplemental contact information (title, address, etc.) and it pulls the wrong info or returns a ?. Here's an example: I have a layout that creates an itinerary for a list of client meetings; these are meetings with the contacts at various firms. The itinerary pulls the meeting date/time, and the contact name, title, and address. I know I've got all that information in the database, I can see it when I look out the contact's full profile. However, on the itinerary, sometimes it cannot pull the title, or it pulls the address for the firm headquarters, not for that specific contact. Actually, "Contact, Title" is a calculated field based on the Meetings 2 table, and is calculated as: Name & ", " & Lookup ( Contacts::Title ), but I still see a handful of "Joe Smith, ?" entries in my itinerary.
It appears at times that the database is pulling that supplemental contact info from the first record created for a firm. For example, if I'm looking at an itinerary that includes a meeting with Mike Jones, an Analyst at Acme, Inc., on the itinerary it might show up as Mike Jones, Manager at Acme, Inc. because the first the contact record created with the Acme, Inc. CompanyID was a Manager.
My question is: is there some other way I should be arranging my data to establish the relationship I'm looking for, or do I just need to tweak a couple calculations and change how certain layouts are associated? If I need to create a ContactID field, how could I retroactively add that to the contact records that already exist? Also, we are in the process of moving to this database after years of separate Excel spreadsheets, so we have about 8,000 meetings entered and about 600 contact records, so would I not be able to link a meeting to a firm without an existing contact record?
Lots of questions, I know, but if I can get an answer to just part of this, I'd be eternally thankful.