Results 1 to 2 of 2
  1. #1
    cmartin is offline Novice
    Windows 2K Access 2007
    Join Date
    May 2010
    Posts
    1

    Error on Multiple Table Join & IIF

    The query below is returning a line 10 extra times when satisfying the IIF clause, so i get 11 entries total per matching record.....advice?

    SELECT tbl_BSecNNM.Rep
    , tbl_BSecNNM.AccountNum
    , tbl_BSecNNM.AccountName
    , tbl_BSecHH.HHID
    , tbl_BSecHH.HHName
    , tbl_BSecNNM.NNMLY
    , tbl_BSecNNM.NNMYTM
    , tbl_BSecNNM.NetNewMoney
    , tbl_BSecNNM.AUM


    , IIf(tbl_BSecFeeAccounts.AccountNum Is Null,"n","y") AS FeeBased INTO tbl_NB_Bsec
    FROM ((tbl_BSecNNM
    LEFT OUTER JOIN tbl_BSecFeeAccounts
    ON
    tbl_BSecNNM.AccountNum=tbl_BSecFeeAccounts.Account Num)
    LEFT OUTER JOIN tbl_BsecHH
    ON
    tbl_BSecNNM.AccountNum=tbl_BsecHH.AccountNum)
    ;

  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Excess records in the query of joined tables is often the result of an improper join or an improper filter of a joined table.

    Rows duplicated in their entirety are the result of improper filtering.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join Key of table in recordset
    By Rohit0012 in forum Forms
    Replies: 14
    Last Post: 09-24-2011, 09:04 PM
  2. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 AM
  3. Replies: 4
    Last Post: 10-26-2009, 05:27 AM
  4. Error using left join accessing 2 active connections
    By peterg2000 in forum Programming
    Replies: 0
    Last Post: 10-05-2009, 05:04 PM
  5. Self Join in a Table
    By LornaM in forum Database Design
    Replies: 10
    Last Post: 05-06-2009, 10:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums