Results 1 to 3 of 3

Thread: [RESOLVED] Junction tables and queries.

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [RESOLVED] Junction tables and queries.

    One of my applications is using tables from an Access database. The tables have a many-to-many relationship that is dealt with by using a Junction table, see below:

    Name:  TableView.jpg
Views: 470
Size:  81.3 KB
    In the Junction table of the dataset I have setup the following query:

    SELECT tblSourceMaster.intSiTechID, tblSourceMaster.strVendorName,tblSourceMaster.strVendorID
    FROM ((lnkItemVendor
    INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID)
    INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)
    WHERE (lnkItemVendor.intSiTechItemID = ?)

    I call the query from a button event in a form:

    LnkItemVendorTableAdapter.FillBySiTechItemID(Me._MasterBase3_0ItemMasterDataSet.lnkItemVendor, glbintIDNum)

    I get the following error:

    Name:  relatedTables.jpg
Views: 431
Size:  58.6 KB

    I can see that the problem is with the two joins after the equal signs. However, I cannot understand why there is no value there. Can anyone explain to me what I am doing wrong. Anything beyond a simple query is not my strong suit.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Junction tables and queries.

    One thing I noticed,
    Code:
    INNER JOIN tblItemMaster ON tblItemMaster.intSiTech = lnkItemVendor.intSiTechItemID) 
    INNER JOIN tblSourceMaster ON tblSourceMaster.intSiTech = lnkItemVendor.intSiTechVendorID)
    I didn't see intSiTech list in either the tblItemMaster or tblSourceMaster

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Junction tables and queries.

    Yeah, I was caught in error yet again (sheepish face imoge). To be fair I am somewhat dyslexic and it took me a really, really long time to find the problem. In the two lines of the of the join you have there I finally saw what was missing. In the ON parameters I had .intSiTech and they should have been .intSiTechID.
    Thanks for the catch. I have been working on this since yesterday morning and could not, for the life of me, see the error in the parameters. I have been running the query since changing that and it provides exactly what I was after.

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
  •  



Click Here to Expand Forum to Full Width