Results 1 to 15 of 15
  1. #1
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    Merging/linking two legacy tables from different departments

    I have two databases, from different departments, that share "some" information.


    1. ContactsDatabase is based on individuals.
    2. CompanyDatabase is based on facilities/locations of companies, with multiple(1-4) PointsOfContact and many AnnualData fields.

    Both databases share the names of some contacts, but not all PointsOfContact are present in both databases.

    CompanyDatabase is a single table in a database and some of the data fields must remain secure. The PointsOfContact in CompanyDatabase must be ranked by status(1-4). ContactsDatabase has multiple tables with additional relationships such as Memberships, EventInvitations, StaffContact, etc...(Most of ContactsDatabase's tables are linked on ContactID#)

    I am lost when it comes to the best way to share the contacts that are found in both. Surprisingly, the users are even less experienced than myself.

    The ContactsDatabase is already split frontend/backend on a shared file server, while the CompanyDatabase is currently in a single table on the same shared file server.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If the CompanyDatabase is multi-user then I would strongly suggest you split it to FE/BE asap.

  3. #3
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    The CompanyDatabase is not currently multi-user. The goal is to make it multi-user, so that the shared-data does not need to be duplicated any longer. There is significant overlap where the CompanyDatabase includes contact info for points of contact that are also listed in the ContactsDatabase.

    My big question is whether it is advisable to shift ALL point of contact info into the ContactsDatabase, then link to the other data tables from there. It is the data tables that must remain secure.

    One problem is that each company has multiple points of contact, which are ranked in status. Additionally, each of those points of contact may be listed under multiple company locations within the CompanyDatabase. So, to shift all point of contact info into the ContactsDatabase, I will have to deal with significant duplication issues. And then successfully link back to companydatabase while keeping its data tables secure.

    I'm a bit confused since these two databases were not originally designed together and I am trying to shift a lot of existing data at this late stage. Little room for error and I don't want to have to do it twice since the FE/BE ContactsDatabase is currently used by the entire office.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I take it the CompanyDatabase is still being updated so it will change over time. Is that correct?

  5. #5
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    That is correct. CompanyDatabase has thus far been a facility-based file maintained by one department. It consists of facility data collected over many years. Each facility has between 1 and 4 points of contact listed. A great number of these points of contact are responsible for multiple facilities, but not at the same status level for the various facilities. (For example, the first point of contact at Facility A may be the second point of contact at Facility B.) This table does include both active and inactive companies. Simply filtering on the active companies has avoided the need to scroll the inactives out into a separate table.

    The idea now is to make the active points of contact viewable by all in the office, by including those names in the ContactDatabse. Only some of the points of contact already appear in both databases. The historical facility data within CompanyDatabase will need to stay in a separate table, viewable only by the single department.

    One goal is to make it possible for a general staffer viewing the ContactDatabase to see a subform which would note that Contact A is 1)Primary point of contact for Company A, located in City, state and also 2)Secondary point of contact for Company B located in city, state.....Not all contacts in the ContactDatabase are affiliated with a company.

    The single department which maintains the facility data needs to be able to view all of the combined information because they will be updating most of the points of contacts for companies.

    The new combined ContactsDatabase will be updated and modified in real-time by many users. The private facility data, along with its relative info in the ContactDatabase, will be maintained by one single department.
    Last edited by jhollingsh615; 04-05-2011 at 10:54 AM. Reason: Omitted detail.

  6. #6
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    Ruralguy, if you are still checking in on this, I'd like to run this by you.
    I am assuming it would be best to append all of the "unique" points of contact from the CompanyDatabaseTable into the ContactsDatabaseTable. Then create a linking table between the two in order to create a many-to-many relationship. That will require a lot of typing, but I only have about 400 unique companies at the moment. I haven't yet counted the unique points of contact, but it is probably manageable.

    One of my concerns is how contacts in the ContactsDatabaseTable will be treated when they do not have any relationship to a company in the CompanyDatabaseTable.

    This is the process I am going to attempt now to see if it works. If you, or anyone else, can tell that I am heading up a dead end, I'd love to hear it. Thanks for any assist/guidance you can offer.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you are responsible for both systems then I would create the query you need in the CompanyDatabase system and that will allow Access to keep it up to date. You can then access this query from the ContactDatabase without impacting or compromising the system.

  8. #8
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    Apologies, Ruralguy,
    Are you saying I am on the right track in appending the unique points of contact from the CompanyDatabase into the more generally used ContactsDatabase? And then creating a many-to-many relationship between the two?

    There are about 2,000 records in the ContactsDatabase. Only some of those are also points of contact in the current CompanyDatabase.

    I am now responsible for both systems design and integration. Many different users add/edit contacts into the ContactsDatabase as part of their daily tasks. I alone make changes to the secure data within the CompanyDatabase.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I was trying to avoid something that would require a periodic scan of a table to see if there are any changes. It should happen naturally as you add or modify either database. There are other reasons to split a system to FE/BE other than multi-user. http://allenbrowne.com/ser-01.html

  10. #10
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    I am happy to split into FE/BE as necessary. ContactsDatabase is already split FE/BE and is working great. I'm just not sure how to now integrate the older CompanyDatabase which "currently" has everything in a single table, some of which (points of contact) are duplicated in the currently-split ContactsDatabase.

    I've never needed a many-to-many relationship before. Forums/books make it sound like this is the way to go. One contact can be tied to many companies. And one company has multiple contacts.

    Maybe I'm being dense and missing something. Right now it sounds much cleaner to have a large ContactsTable with a many-to-many relationship to a smaller CompanyTable. But, I'm not sure how this would best be accomplished since all of the data is already in tables and I will still need to keep some information in the CompanyTable secure.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by jhollingsh615 View Post
    One contact can be tied to many companies. And one company has multiple contacts.
    Can a contact be a contact for more than one company? That is what would make it a many-to-many relationship.

  12. #12
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    Yes, many of the contacts are linked to multiple facilities. Think of it as different facilities that are sister-companies. Sometimes a contact is the primary contact at one company and a secondary contact at another company.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do both tables have PrimaryKeys?

  14. #14
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    Two legacy databases

    Ruralguy,
    I hope this might help explain what I am trying to do. ContactsDatabase is used by everyone and they need to see the PointOfContact(POC) info that is in the CompanyDatabaseTable.

    The CompanyDatabase has only one table. But, each table is poorly done and includes many repeated fields, such as poc1name, poc2name, poc1email, poc2email....etc.....

    My current plan is to spin off all of the POC info and append it to the main ContactsDatabase. Then, I would add the CompanyDatabase as a new table within the ContactsDatabase. Then create a many-to-many link between the relevant contacts and the companies they supervise.

    Does that make any sense? Or am I setting myself up for a big headache later on?

    I recognize and take blame for the condition of the current CompanyDatabase. It is far from being normalized and I just need to find the best way to do so, while integrating it into the broader ContactsDatabase.

    Apologies for the convoluted dialogue.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I believe you are on the right track. The closer you get the tables to normalized, the easier it will be to see what is needed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  2. Set command problem in legacy system
    By ch875299 in forum Programming
    Replies: 8
    Last Post: 02-02-2010, 04:59 PM
  3. Linking Tables
    By Richard Hale in forum Access
    Replies: 0
    Last Post: 01-25-2010, 02:09 PM
  4. Merging 2 tables
    By todavy in forum Queries
    Replies: 4
    Last Post: 12-02-2009, 10:12 AM
  5. Replies: 8
    Last Post: 11-04-2009, 04:22 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums