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

    Unanswered: problem querying three linked tables

    A bit of a beginner to all this database stuff, but I'm learning...

    I am trying to write an Access query getting data from three tables:

    tblCaseList
    - FileNumber(manually entered, primary key)
    - Attorney
    tblContacts
    -ID (autonumber, primary key)
    - Name
    tblFileContacts
    - ID (autonumber, primary key)
    - FileNumber (liked to the FileNumber of the tblCaseList)
    - Name (linked to the ID of the contact in tblContacts)

    Because a contact can be associated with several files, I set up the join table of tblFileContacts to link to the file number and to a contact in the contacts table.

    I am trying to pull a query with the following information:

    tblCaseList.FileNumber
    tblCaseList.Attorney
    tblFileContact.Name

    It works fine as long as there is a contact for the file number in the tblFileContacts table. If there is no contact, it does not pull that file.

    Is there a way to pull the file number and attorney while leaving the contact blank if there is none?

    Thanks, sorry for the duplicate post, I accidentally hit return twice while writing this
    Last edited by goldrhim; 01-27-04 at 16:01.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: problem querying three linked tables

    Originally posted by goldrhim
    A bit of a beginner to all this database stuff, but I'm learning...

    I am trying to write an Access query getting data from three tables:

    tblCaseList
    - FileNumber(manually entered, primary key)
    - Attorney
    tblContacts
    -ID (autonumber, primary key)
    - Name
    tblFileContacts
    - ID (autonumber, primary key)
    - FileNumber (liked to the FileNumber of the tblCaseList)
    - Name (linked to the ID of the contact in tblContacts)

    Because a contact can be associated with several files, I set up the join table of tblFileContacts to link to the file number and to a contact in the contacts table.

    I am trying to pull a query with the following information:

    tblCaseList.FileNumber
    tblCaseList.Attorney
    tblFileContact.Name

    It works fine as long as there is a contact for the file number in the tblFileContacts table. If there is no contact, it does not pull that file.

    Is there a way to pull the file number and attorney while leaving the contact blank if there is none?

    Thanks, sorry for the duplicate post, I accidentally hit return twice while writing this
    This Select can help you:
    SELECT tblCaseList.FileNumber, tblCaseList.Attorney, tblFileContacts.FileNumber, tblContacts.Name
    FROM tblContacts RIGHT JOIN (tblCaseList LEFT JOIN tblFileContacts ON tblCaseList.FileNumber = tblFileContacts.FileNumber) ON tblContacts.ID = tblFileContacts.Name;
    Saludos
    Norberto

Posting Permissions

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