Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Compare two queries

    I have 2 variable newsql3 and newsql9, the relevant query is below;

    SELECT tblBugs.BugsID, tblBugs.DateB, tblBugs.Clarify_Case, tblBugs.DescriptionB, tblBugs.NotesB FROM tblBugs ORDER BY tblBugs.BugsID;

    tblBugs.BugsID is the keyID


    here is the relevent code;

    Private Sub IOS_AfterUpdate()


    If (Me.IOS <> "") Then
    lastsql3 = Me.ListBugs.RowSource 'hold just in case - IOS_only2 searches for IOS name rather than full image
    newsql3 = BuildFilteredSQL(Me.IOS, "IOS_only2", OriginalSQL2) 'build new query show row source with IOS release
    Me.ListBugs.RowSource = newsql3
    Searched_list = newsql3 'search on sub-list created by IOS dropdown menu
    Me.cmdShowAll.Visible = True 'show full list button following sub-list creation
    Call RequeryList
    End If
    End Sub

    Private Sub CmdCompare_Click()
    If (Me.IOS2 <> "") Then

    lastsql9 = Me.ListBugs.RowSource 'hold just in case - IOS_only2 searches for IOS name rather than full image
    newsql9 = BuildFilteredSQL(Me.IOS2, "IOS_only2", OriginalSQL2) 'build new query show row source with IOS release
    Me.ListBugs.RowSource = newsql9
    Searched_list9 = newsql9 'search on sub-list created by IOS dropdown menu
    Me.cmdShowAll.Visible = True 'show full list button following sub-list creation


    Call RequeryList
    End If
    End Sub


    So newsql3 and newsql9 are search results. What I want to do is compare these two search results, and change the Me.ListBugs.Rowsource to show either all matching records, or all non-matching records,,

    Any help will be greatly appreciated!!!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How do you "match" these results? By the key column only or are you intending to compare all columns?
    Also, when you don't match do you want:
    1) all rows from newsql3 that do not appear in newsql9
    2) all rows from newsql9 that do not appear in newsql3
    3) all the rows from 1) and all the rows from 2)
    ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also note that

    If (Me.IOS2 <> "") Then


    is only checking whether or not Me.IOS2 is a Zero-length string. If the control is simply empty, it is probably Null, not a Zero-length string. Better to use

    If Not Nz(Me.IOS2,"") = "" Then
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Dec 2009
    Posts
    8
    thanks for the replies so far,

    I need to match on the tblBugs.BugsID column,

    I need one button to press which lists compares newsql3 and newsql9 all records with matching tblBugs.BugsID to be displayed as Me.ListBugs.RowSource.

    And another button to display all the lines that don't match..

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - we'll try a different tack.
    Code:
    SELECT *
    FROM newsql3 INNER JOIN newsql9 ON  newsql.BugsID = newsql9.BudsID
    That get you all rows that match on BugsID. Please run that and tell us if that is what you want, and if not precisely how you want it to behave differently.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2009
    Posts
    8
    thanks, that is pretty much what I am after,

    I have tried the below, but neither work, just bring back an empty list;

    Me.ListBugs.RowSource = "SELECT FROM newsql3 WHERE newsql9.BugsID = newsql3.BugsID"

    And,

    Me.ListBugs.RowSource = "SELECT * FROM newsql3 INNER JOIN newsql9 ON newsql3.BugsID = newsql9.BugsID"

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The first will result in an error, not return an "empty list".
    The second should work. If it returns nothing then you have no matching rows.

    In any event, I would test this SQL in the query window not the form. Only once you have finalised your SQL should you put it in to the form.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2009
    Posts
    8
    the trouble is that newsql3 and newsql9 are not queries they are variables with query parameters, i think?;

    so looking with newsql3 for example;

    newsql3 = BuildFilteredSQL(Me.IOS, "IOS_only2", OriginalSQL2)

    Me.IOS is what is to be found in 'OriginalSQL2' which is from the below query;

    SELECT tblBugs.BugsID, tblBugs.DateB, tblBugs.Clarify_Case, tblBugs.DescriptionB, tblBugs.NotesB FROM tblBugs ORDER BY tblBugs.BugsID;

    so the result of this, is what I want to compare with newsql9 which is exactly the same, only there is a different value for Me.IOS

Posting Permissions

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