Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Question Unanswered: Query with two joins??

    Hi there. I am having trouble getting a query to execute in Access 2000. I have attached two screen shots to illustrate my problem.

    The background: I have a 'jobs' table (it's a maintenance database) which holds open and closed maintenance job for the factory. This table holds the job data, and also indexes into the Users table, which contain the user information of the person that entered the job, and ALSO an index into the Hardware table, which holds the information on the item of hardware that is being reported.

    I can produce a query with a simple join, which will join the Maintenance table with the Users table, to correctly retrieve the name of the person that entered the job, but I ALSO need to join the table with Hardware table to retrieve the hardware data.

    Can anyone help?? The attached screen shots of the Access query designer shoulud illustrate nicely what i'm trying to do, but the query designer seems unable to interpret what I'm trying to do when I apply two joins...

    Here is the SQL that Access generates for the first Query (Maintenance table joines to Users table to retrieve user name also):

    SELECT tblMaintenance.JobNumber, tblUsers.UserName, tblMaintenance.JobDate, tblMaintenance.Notes
    FROM tblUsers INNER JOIN tblMaintenance ON tblUsers.ID = tblMaintenance.ReportedByID
    ORDER BY tblMaintenance.JobNumber DESC;

    And here is the second query (clearly wrong), which is supposed to join the Hardware table with the Users table, to retrieve the name, and ALSO the Hardware table, to retrieve the name of the hardware item:

    SELECT tblMaintenance.JobNumber, tblUsers.UserName, tblMaintenance.JobDate, tblMaintenance.Notes, tblHardware.HardwareItem
    FROM tblHardware INNER JOIN (tblUsers INNER JOIN tblMaintenance ON tblUsers.ID = tblMaintenance.ReportedByID) ON (tblUsers.ID = tblHardware.ID) AND (tblHardware.ID = tblMaintenance.HWItemID)
    ORDER BY tblMaintenance.JobNumber DESC;

    This is a real brain stormer for me!! Any help greatly appreciated!!!

    Mark Wills.
    Attached Thumbnails Attached Thumbnails query1.JPG   query2.JPG  
    Mark Wills.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access threw in an extra relationship because the IDs had the same name --

    tblUsers.ID = tblHardware.ID

    clearly wrong

    dunno how to stop it from doing that, but when it happens, i just click on that extra line in the relationship diagram and delete the relationship

    Code:
    select tblMaintenance.JobNumber
         , tblUsers.UserName
         , tblMaintenance.JobDate
         , tblMaintenance.Notes
         , tblHardware.HardwareItem
      from (
           tblMaintenance 
    inner 
      join tblUsers 
        on tblMaintenance.ReportedByID 
         = tblUsers.ID
           )
    inner
      join tblHardware 
        on tblMaintenance.HWItemID 
         = tblHardware.ID
    order 
        by tblMaintenance.JobNumber desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    My god that was fast!!! Thanks SO much.... You've been a BIG help!!!! :-)

    Mark.
    Mark Wills.

Posting Permissions

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