1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Combining two data sources to populate one field?

Discussion in 'Business Applications' started by Danerys, Jul 6, 2007.

Thread Status:
Not open for further replies.
  1. Danerys

    Danerys Thread Starter

    Joined:
    Jul 6, 2007
    Messages:
    1
    I am writing a database in Access 2003 using Windows XP Pro.

    I have two separate tables - one that contains identifiers for people (last, first, middle, dob, ssn, etc.) another that contains information for companies (name, state, date, number for incorp, etc.)

    In various different portions of the database, these tables are used individually. For instance, the two tables are linked (through a separate table ) to relate people to companies that they are associated with and identify their roles.

    Another table in the database contains certain information about civil and criminal lawsuits. One of the fields in that table - "defendants" - needs to be able to store a link to EITHER the person table OR the company table (defendants can be either people or companies)

    I created a joined table that contains both the people and the companies in one field. But, although this will allow me to ease data entry, it does not actually create a link back to the source table - using the primary key from both tables in the join creates a field where there are two 1's, 2's, etc. and no way to uniquely identify the information. :confused:

    I created a table that contains as field one a unique identifier, as field two the people table id, as field three the company table id. I then created a query off that table with a concatenated field of first, middle, last, company (so that it would show either the name or the company name.) The query does not work because, by design, either the person or company field in the underlying table is null and the query will only show those records where both fields are not null. :confused:

    The only solution I see is to put the company and person info into one table, using either person or company (and linking from that table to that table in order to relate people and companies for other purposes.)

    Does anyone have thoughts on how to accomplish this BEFORE I redo the entire database structure to accomodate this ???:(
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Danerys, can you please post or email me a zipped copy of the database.
    It can either have no data a a little dummy data for me to work with.
    I will take a look at it for you.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/592645

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice