Results 1 to 14 of 14
  1. #1
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Query With Junction Table Only Returns Records With Matching Records In Junction Table

    Hi Guys,



    I have a query issue. The query executes and only returns records that exist in both the primary table and the junction table this is normal and expected but the situation is this, not all records in the primary table will have a related record(s) in the junction table. I know there is a trick to getting Access to show all records but I can't remember what it is. I would appreciate it if someone could jog my memory on how to deal with this issue

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,485
    Edit the join between the tables by double or right clicking on the join line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    I did and neither of the other options produced the desired result

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,485
    What is the SQL of the query? I'd expect:

    FROM PrimaryTable LEFT JOIN OtherTable ON...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    This is the query

    Code:
    SELECT tblCards.[Card ID], tblCards.[Card Name], tblCards.[Category ID], tblCards.[Attribute ID], tblCards.[Monster ID], tblCards.Level, tblCards.[Attack Points], tblCards.[Defense Points], jctCardTypes.[Card ID], jctCardTypes.[Type ID], tblCards.[Pendulum Scale], tblCards.Link, tblCards.Description, tblCards.Forbidden, tblCards.Forbidden, tblCards.[File Name]
    FROM lkpTypes INNER JOIN ((lkpMonsters INNER JOIN (lkpAttributes INNER JOIN (lkpCategories INNER JOIN tblCards ON lkpCategories.[Category ID] = tblCards.[Category ID]) ON lkpAttributes.[Attribute ID] = tblCards.[Attribute ID]) ON lkpMonsters.[Monster ID] = tblCards.[Monster ID]) INNER JOIN jctCardTypes ON tblCards.[Card ID] = jctCardTypes.[Card ID]) ON lkpTypes.[Type ID] = jctCardTypes.[Type ID];

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,485
    Doesn't look like you changed the join. INNER would be LEFT or RIGHT.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    This is the original query. Changing the joins resulted in a message about it being ambiguous.

  8. #8
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    Junction table is jctCardType which is junction of the primary table tblCards and a lookup table lkpTypes. The database is a Yu-Gi-Oh Trading Card database. tblCards contains info about each card. lkpTypes is a lookup for the various card types. Some cards have no type while others may have one or more. Some examples are Effect, Fusion, Ritual, and XYZ

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,485
    So is it "didn't produce the desired result" or threw an error? All of the joins probably have to be changed. If that doesn't work, can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    It threw an en error. Attaching the DB probably not a good idea since it's BIG due to BLOB data being stored.

  11. #11
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    I tried to upload a virgin copy of the database with no data but it would not upload. I tried multiple times

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,485
    Hard to test a query with no data anyway. Did you compact and then zip?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    No, the DB stands at 1.88 GB with the BLOB data alone . It's a big one and no real data has been entered. That's why I tried to upload a virgin copy. I figured you could at least see the database relationships. Tables are easy to identify by their names. Primary tables are prefixed with tbl, lookup tables with lkp and junction tables with jct.

  14. #14
    blakebaker008 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2017
    Posts
    3
    iI attempted to upload a virgin reproduction of the database and not using facts however it might no longer add. I tried more than one times

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

Similar Threads

  1. Replies: 3
    Last Post: 02-29-2016, 03:26 PM
  2. Replies: 4
    Last Post: 07-14-2015, 06:49 PM
  3. Replies: 4
    Last Post: 02-04-2014, 10:41 AM
  4. How to query data from junction table?
    By bigal0043 in forum Queries
    Replies: 1
    Last Post: 05-24-2013, 03:03 AM
  5. Date Lookup in Query for Junction Table
    By Phasma in forum Access
    Replies: 2
    Last Post: 01-21-2011, 03:36 PM

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