Comparing two queries

[test2000]

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

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

End If
End Sub

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

newsql9 = BuildFilteredSQL(Me.IOS2, "IOS_only2", OriginalSQL2)
Me.ListBugs.RowSource = newsql9

End If
End Sub

This is what the bit of the module the queries are calling;
Case "IOS_only2"
sqlwhere = "WHERE ((tblIOSaka.IOSakaID Like '*" & filtertext & "*'))"

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, matching will be on
Any help will be greatly appreciated!!!!

 

[jal]

If you want to use a WHERE clause, you can append it to your query. For example

SELECT tblBugs.BugsID, tblBugs.DateB, tblBugs.Clarify_Case, tblBugs.DescriptionB, tblBugs.NotesB FROM tblBugs ORDER BY tblBugs.BugsID
WHERE tblBugs.BugsID > 50

However, you proposed the following WHERE clause:


WHERE ((tblIOSaka.IOSakaID Like '*" & filtertext & "*'))"


The problem is that you are now introducing a second table. Typically this means you want to join the tables on a column that has values in common. Typically this kind of query is created in the query wizard by simply selecting those two tables (assuming you have drawn a connecting line between the column-in-common in Relatiinships View). So let's say you saved this query under the name qryJOIN. You can now add you WHERE clause like this:

listBox1.RowSource = "SELECT FROM qryJOIN " & newSQL

which means

listBox1.RowSource = "SELECT FROM qryJOIN WHERE ((tblIOSaka.IOSakaID Like '*" & filtertext & "*'))"


I'm not sure if I'm understanding your problem.

 

[test2000]

thanks Jal for your reply,

sorry if my problem description is a bit vague..

basically ... this would solve my problem, if it were possible(?);

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

The issue is that newsql3 and newsql9 are variables from search results from the original query at the top of my first message. The comparison is regarding data within the same tables....

For example the original query brings back this;

a b c d e 1
f g h i j 2
k l m n o 3
p q r s t 1
u v w x y 2

then I search on '1'
a b c d e 1
p q r s t 1

and save to newsql3

then I search on '2' and get
f g h i j 2
u v w x y 2

and save to newsql9

So what I now want to do is compare newsql3 and newsql9 just on one column, say column 1...

so in this instance match will be NULL, and differences if comparing newsql3 with newsql9 will be the below;

RowSource =
a b c d e 1
p q r s t 1

Hope this makes sense.

 

[jal]

You say you want to write:

ListBox1.Rowsource = "SELECT FROM newsql3 WHERE newsql9.BugsID = newsql3.BugsID"

Yes, this is valid VBA code if, and only if, newSql3 and newsql9 are tables existing in your database, or if they are queries saved under a name (SELECT queries saved under a name). You can also name and save a query on the fly like this:

Dim qDef as DAO.QueryDef
Set qdef = CurrentDB.CreateQueryDef("newSQl9")
qdef.SQL = "SELECT ....."

Assuming so, you can write:

ListBox1.RowSource = "SELECT * FROM newsql3,newSql9 WHERE newsql9.BugsID = newsql3.BugsID"

which is the same as

ListBox1.RowSource = "SELECT * FROM newsql3 INNER JOIN newSql9 ON newsql9.BugsID = newsql3.BugsID"


This would return those rows where the two tables match on BugsID. I'm not sure if this is what you mean by "comparing tables".

 

[test2000]

Hi Jal,

Ah, yes this is exactly what I need. Though as you say the queries are saved under a variable name. I have tried creating the query on the fly as you mention, and that worked once, though as soon as the code runs again it complains that the query already exists so cannot be re-created..

Just wondering if I can overwrite the query..

Note that I am not comparing tables, just two variables with query results.

 

[test2000]

Think I am finally getting somewhere thanks to you...

I have added the below to delete the query each time and it looks to be working.
DoCmd.DeleteObject acQuery, ("newSQL3")

Now I need to do the same, though this time for non-matching ...

I have tried the below;
Me.ListBugs.RowSource = "SELECT * FROM newSQL3 INNER JOIN newSQL9 ON newSQL9.BugsID <> newSQL3.BugsID"

This gives me a repeated amount of records, ie. 1, 3, 5, 1, 3, 5, 1, 3 when it should just give 1, 3.

Me.ListBugs.RowSource = "SELECT * FROM newSQL3,newSQL9 WHERE (newSQL9.BugsID != newSQL3.BugsID)"

This one doesn't give any records...

Also... when comparing the two files, if there are different records which are displayed when I finally get this working, I want to determine which record is having the extra record ... is it newsql3 or newsql9.. because if I use 'not equal to' it will just list all the different records, but I won't be able to tell which list has it or not... any ideas?

 

[test2000]

got it...

Me.ListBugs.RowSource = "SELECT * FROM newSQL3 WHERE (newSQL3.BugsID NOT IN (SELECT newSQL9.BugsID FROM newSQL9))"

just the job.. im happy now

 

[WayneRyan]

Test,

As you start getting more data, you'll find that Access really doesn't like
the Nested Select statement and REALLY SLOWS down.

An alternative.

Me.ListBugs.RowSource = "SELECT newSQL3.* " & _
                        "FROM newSQL3 Left Join newSQL9 On " & _
                        "       newSQL3.BugsID = newSQL9.BugsID " & _
                        "WHERE newSQL9.BugsID Is Null"

That will run a lot faster.

hth,
Wayne

 

[jal]

Think I am finally getting somewhere thanks to you...

I have added the below to delete the query each time and it looks to be working.
DoCmd.DeleteObject acQuery, ("newSQL3")

To avoid crashes, suspend error-checking

On Error Resume Next 'suspends error checking
CurrentDb.QueryDefs.Delete ("newSql9")
On Error GoTo 0 'resumes exceptions

 

[test2000]

thanks guys, I will integrate your suggestions..