Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    17

    Unanswered: Trouble building an SQL query

    I'm having trouble trying to build an SQL query. I have a table [tblBooks] with a list of all the books we manage and we have another table [tblOpinion] which lists the opinion made by each individual reviewer of that book. I need a query to show me a list of all books either not yet reviewed by a particular reviewer (i.e. no matching record in [tblOpinion]) or whether they haven't finished writing their review (this is true if the field [tblOpinion].[ClosedBy] is empty). I can print out what I want using VBA but ideally I need a query so I can fill a listbox with the results.

    Code:
        Dim recDoc, recRev As Recordset
        Dim intReviewerID As Integer
        
        intReviewerID = 2
        
        Set recDoc = CurrentDb.OpenRecordset("SELECT BookID, Name FROM tblBooks", dbOpenSnapshot)
        Do While Not recDoc.EOF
            Set recRev = CurrentDb.OpenRecordset("SELECT OpinionID, ClosedBy FROM tblOpinion WHERE BookID = " & _
                         recDoc("BookID") & " AND ReviewerID = " & intReviewerID, dbOpenSnapshot)
            If recRev.RecordCount = 0 Then
                Debug.Print recDoc("BookID"), recDoc("Name")
            ElseIf IsNull(recRev("ClosedBy")) Then
                Debug.Print recDoc("BookID"), recDoc("Name")
            End If
            recRev.Close
            recDoc.MoveNext
        Loop
        recDoc.Close

    Thank you for any help,
    Stephen.
    Last edited by sgoldsmith; 05-28-04 at 10:43.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Something like this might do..

    SELECT BookID, Name
    FROM tblBooks LEFT JOIN tblOpinion ON tblBooks.bookID = tblOpinion.bookID AND tblOpinion.ReviewerID = " & intReviewerID & " WHERE (tblOpinion.ReviewerID IS NULL OR tblOpinion.CloseBy IS NULL)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    17
    Quote Originally Posted by Teddy
    SELECT BookID, Name
    FROM tblBooks LEFT JOIN tblOpinion ON tblBooks.bookID = tblOpinion.bookID AND tblOpinion.ReviewerID = " & intReviewerID & " WHERE (tblOpinion.ReviewerID IS NULL OR tblOpinion.CloseBy IS NULL)
    Thanks Teddy. Unfortunately, Access complains that the join expression is not supported for the "tblOpinion.ReviewerID = 2" part. Is this a problem with Access' non-standard SQL?

    Thanks,
    Stephen.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What context are you using the query in? I don't think you can reference variables directly from a query. I figured that would be for use in VBA.

    Could you post the context and/or exact code you're attempting to use?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Posts
    17
    Teddy,

    Thanks again for your reply and I hope you have had a good weekend. Anyways, I am planning to set the listbox .RowSource to an SQL string in Form_Current, so yes it will be done in VBA. To test the query, however, I am writing the SQL in a query window and have just replaced the intReviewerID with a known static value (2 in this case).

    Thanks again,
    Stephen.

Posting Permissions

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