Access 97 Problem...

[lychew]

I'm using Microsoft Access 97.

Question:

there's 70 records in Qry1 and 25 records in Qry2.
25 records in Qry2 are identical to the records in Qry1.

SQL Statement:
"select * from Qry1, Qry2 where Qry1.Field1<> Qry2.Field2"

It returns 1750 records instead of only 55 records. It seems like the <> statement is not working. Why?? How do i get the 45 records which is in Qry1 but not in Qry2??

[Clunietp]

You get 1750 records because 25*70 = 1750 -- it will show each record where they are not equal in each record....

Try this (I didn't test it, but it should do)

Select * from Qry1 left join qry2 on qry1.field1 = qry2.field2 where qry2.field2 is null

I think that's right....yes?

[FirstKnight]

Actually it should look like this...

SELECT Qry1.*, Qry2.*
FROM Qry1 INNER JOIN Qry2 ON Qry1.Field1 = Qry2.Field1;

Here's a tip...

Open Access and design your query there then Select View from the menu bar and select SQL-View from there. It pops up a windo with the SQL statement for the query that you just created, and you can use that in VB. Thats how I came up with the above statement )

Don't thank me, thank Karl Moore and his Database tutorial

[lychew]

Thanks firstKnight...
The statement works perfectly if it's an "=".
But when you use "<>", it first join those 2 tables together and create a new recordset with 1750 rows. When you use the "<>" statement with the distinct statement, all the 70 rows will come out...if you don't use the distinct statement, then 1725 rows will be in the query...
I haven't tested the statement from Clunietp.
I'll try it tomorrow in the office.Thanks.

[HarryW]

Karl Moore's database tutorial.... is that on the internet? If so where can I find it?

Thx.

[Clunietp]

by using an INNER JOIN, you will display all records that are in both tables.

by using a LEFT JOIN, you will see all records in table1 and a corresponding matching record from table2 if there is one, otherwise you will get a null for the table2 fields. (which is what I specified as a condition, so we get the ones that DO NOT have a match in the second table)

the use of the LEFT JOIN will give lychew what he needs to get done, inner join will not help him here.

lychew wants the records that DO NOT have a match in table2. If he wanted a match, INNER JOIN is the winner.

[JohnAtWork]

*grins*
The easiest way to do this is Clunie's.

If you want a step-by-step method, however, go to Query, New, Find Unmatched Wizard.

This will walk you through step by step.
The end result, however, will be Clunie's SQL statement.

[lychew]

Thanks guys.
Both the methods work perfectly.

[FirstKnight]

I apologise, but I misread lychew's question. I will have to agree with you Clunietp

As for Karl Moores Database Tutorial, you can find it right here on VB-World. Just go to the articles.

------------------
Today is the last day of your past

[lychew]

Thanks again...