Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    12

    Red face Unanswered: Pulling info from two tables

    Ok, I've been having problems with pulling data from two tables for quite some time. I can't seem to get it into my thick skull how to do it. I will give you as much detailed information as I possible can. I sincerely appreciate any help anyone can provide to me!

    I have three tables:

    tblCaseList
    tblContacts
    tblFileContacts

    I work at a small law firm and have developed a database for them to store their file information.

    tblCaseList has a primary key field of "FileNumber" (since no two file numbers can be the same).

    tblContacts has an autonumber primary key. This table holds contact information for various clients and company contacts.

    tblFileContacts is a joining table so that I don't have to have "Joe Smith" listed in tblContacts 10 times if he's associated with 10 different files. This table has an autonumber primary key as well, along with a "FileNumber" field (linked to the primary key on tblCaseList), and a "Contact" field that links to the primary key of the tblContacts table.

    If I am searching for a contact name, I have a query pulling their name and what file they're associated with. My problem is, if the contact is not associated with a file at all, nothing pulls. I need it to pull the contact, but have a blank in the "FileNumber" field of the query if there is no entry for that in tblFileContacts.

    I know there is something to do with joins here, but to be honest, I don't know how to make this happen.

    Again, I sincerely appreciate any help anyone can give me. I need to make sure that all data is correct before we launch this to the rest of the firm.

    Sincerely,
    Tim

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    My problem is, if the contact is not associated with a file at all, nothing pulls.
    ----------------

    Hmm, sounds like the "Find Unmatched query wizard". Find it in the new query popup window.

    HTH

  3. #3
    Join Date
    Dec 2003
    Posts
    12
    The only problem is, I don't want to run this every once in a while through that query. I have a form with a text box and a command button where the user enters a last name and hits the command button to run the query. The query's SQL is listed below (I desgned it in the design view, so if the SQL is the problem, maybe we can start there):

    SELECT tblContacts.ContactID, tblFileContacts.[File Number], tblContacts.FirstName, tblContacts.LastName, tblFileContacts.Notes, tblContacts.WorkPhone, tblContacts.WorkExtension, tblContacts.FaxNumber, tblContacts.EmailName
    FROM tblContacts INNER JOIN tblFileContacts ON tblContacts.ContactID = tblFileContacts.Contact
    WHERE (((tblContacts.LastName) Like "*" & [Forms]![frmContactBy]![txtLastName] & "*"));

  4. #4
    Join Date
    Mar 2004
    Posts
    7
    Change the join of your query to be:

    Include all Records from tblContacts and only those from tblFileContacts where they match.

    Do this by right mouse clicking on the Join Line and select Join Properties

    HTH

  5. #5
    Join Date
    Dec 2003
    Posts
    12
    And what do you know... it works!

    Thank you so very much! I've been struggling with that for a long time, hahaha. I greatly appreciate it! You've just lifted a great weight from my shoulders!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •