Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20

    Unanswered: Missing records in query

    Hi. I have a database with three tables, all in a relationship. This is the SQL from the query

    SELECT tblBook.BookID AS tblBook_BookID, tblBook.Book, tblBook.Price, tblBook.Frequency, tblName.NameID AS tblName_NameID, tblName.Fname, tblName.Surname, tblName.TelNo, tblOrder.ID, tblOrder.NameID AS tblOrder_NameID, tblOrder.[No], tblName.Cancelled
    FROM tblName INNER JOIN (tblBook INNER JOIN tblOrder ON tblBook.BookID = tblOrder.BookID) ON tblName.NameID = tblOrder.NameID
    ORDER BY tblName.Surname;

    What I am finding is that not all the records are showing in the query. From looking at previous posts and at this tutorial SQL Tutorial [SELECT Statement Extended] I think it may be something to do with the Join function. Perhaps it should be FULL OUTER JOIN? But I am not sure how to code this to replace the two join statements above. If I replace INNER JOIN with FULL OUTER JOIN an error message says Syntax error and highlights OUTER.

    Perhaps someone can explain this to me.
    Janet

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    It appears your tutorial site you linked to does a decent enough job of explaining the differences, but I don't think Access supports a Full Outer Join; I think you'll want either a LEFT JOIN or a RIGHT JOIN.
    Me.Geek = True

  3. #3
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    OK. So I tried LEFT OUTER JOIN and LEFT JOIN (as you suggested nckdryr) both gave the error message "Join expression not supported".

    If that is the case, how do I get these records visible?
    Janet

  4. #4
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    I've just seen your reference to the ACCESS tutorial on joins. I have tried what they suggest, but it is telling me I need to create a query on one join and then insert it into the other query with the other join. I shall have to have a go at that tomorrow. I am not sure how to do it, but I will give it a try.
    Janet

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Try something like the following:

    Code:
    SELECT tblBook.BookID AS tblBook_BookID, tblBook.Book, tblBook.Price, tblBook.Frequency, tblName.NameID AS tblName_NameID, tblName.Fname, tblName.Surname, tblName.TelNo, tblOrder.ID, tblOrder.NameID AS tblOrder_NameID, tblOrder.[No], tblName.Cancelled
    FROM tblName LEFT JOIN (tblBook LEFT JOIN tblOrder ON tblBook.BookID = tblOrder.BookID) ON tblName.NameID = tblOrder.NameID
    ORDER BY tblName.Surname;
    See if that gets you what you're after.
    Me.Geek = True

  6. #6
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    Tried that and the error "Join expression not supported" comes up
    Janet

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
      FROM (
           tblOrder
    INNER
      JOIN tblBook
        ON tblBook.BookID = tblOrder.BookID
           )
    INNER 
      JOIN tblName 
        ON tblName.NameID = tblOrder.NameID


    they ~have~ to be INNER joins, unless you have a situation where (1) there's an order with a BookID for a non-existent book, or (2) there's an order with a NameID for a non-existent name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    BINGO. That worked. Many thanks.

    It is basically the same as I had originally but with the brackets in a different place.

    'Tis a puzzlement!
    Janet

  9. #9
    Join Date
    Sep 2010
    Posts
    1

    MS Access 2007 - Query problem

    I have a similar problem as mentioned above and the solution does not work for me. I have two tables; one of them has 1400 records and another about 100. It seems that very randomly, query does not give the record I call for,
    Here is the SQL code for my query. Please give me any idea what is wrong or what should I change to make it work. MS Access 2007

    Code:
    SELECT MasterListing.OWC, MasterListing.ID, MasterListing.PartNumber, MasterListing.SerialNumber, MasterListing.WUC, MasterListing.DateDueCal, MasterListing.DateLastCal, MasterListing.Location, MasterListing.Comments, MasterListing.Nomenclature, MasterListing.JobControl, MasterListing.CalInterval, MasterListing.DateFromPmel, Master_PN.PartNumber, Master_PN.Nomenclature, Master_PN.CalInterval, Master_PN.LastUpdate, Master_PN.Remarks, Master_PN.WUC, MasterListing.Status, MasterListing.DateToOWC
    FROM MasterListing INNER JOIN Master_PN ON MasterListing.PartNumber = Master_PN.PartNumber
    WHERE (((MasterListing.ID)=[Enter ID]));

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
  •