Very simple query problem

[BenH]

Hi,

I'm a bit new to queries in Access, and normally use SQL in Ingres to get what I need.I found the most simplest of things to do actualy quite difficult in the Query Design View of Access.

Say I have two non keyed tables with exactly the same data structure. There is only one field, a 5 digit Text field which is a reference, called 'reference' They're called Table1 and Table2. The majority of the data is the same in both of the tables, with only slight differences. There may be duplication of a entry in the same table, the fields are not unique so repetition may occur

I want to find all the entries in table1 do not exists at all in table2. This would be easy to acheive in Ingres SQL, but I can't find an easy way in Access.

I'd do this with a subquery in Ingres as below

select reference
from table1
where reference not in (select reference
from table2);

or

select reference
from table2
where reference not in (select reference
from table1);

What if there were many more fields also.

 

[MSAccessRookie]

Hi,

I'm a bit new to queries in Access, and normally use SQL in Ingres to get what I need.I found the most simplest of things to do actualy quite difficult in the Query Design View of Access.

Say I have two non keyed tables with exactly the same data structure. There is only one field, a 5 digit Text field which is a reference, called 'reference' They're called Table1 and Table2. The majority of the data is the same in both of the tables, with only slight differences. There may be duplication of a entry in the same table, the fields are not unique so repetition may occur

I want to find all the entries in table1 do not exists at all in table2. This would be easy to acheive in Ingres SQL, but I can't find an easy way in Access.

I'd do this with a subquery in Ingres as below

select reference
from table1
where reference not in (select reference
from table2);

or

select reference
from table2
where reference not in (select reference
from table1);

What if there were many more fields also.

Sorry to say this, but without primary keys to join the tables on, your approach is correct, and may be the only possible resolution. If you had primary keys, then you might want to consider DISTINCTROW selection. One shortcoming is that DISTINCTROW will tell you that there is something different, but it will not identify what the difference actually is.

 

[BenH]

OK, so those queries will work in the SQL view of the Query Design?

If relationships are set up in the design view between the two fields, would this help me write a query to resolve this problem? I could set the fields to be keys with duplicates.

Cheers.

 

[MSAccessRookie]

OK, so those queries will work in the SQL view of the Query Design?

If relationships are set up in the design view between the two fields, would this help me write a query to resolve this problem? I could set the fields to be keys with duplicates.

Cheers.

Those queries (as you wrote them) should work in the SQL View of the Query Design. In fact, a lot of Standard INGRES and ORACLE Queries will work there with little or no modifications required. What you do with them will have to be be up to you, since not enough details of your project have been provided at this time for me to make an informed recommendation.

 

[pbaldy]

You might also try the Unmatched query wizard. The join method would typically be more efficient than a subquery.

 

[BenH]

Thanks for these responses. The Unmatched Query wizard acheieves what I want. I also did not know that the Ingres query would work.