How to compare and identify data in array?

[sg2808]

I have two columns which holds numerical values and both the columns are sorted.
I want to check if the data in the first column exists in the second column or not?

Eg.
Column A
1
2
3
4

Column B
1
2
4
5

I would like to check if the 1st cell value(=1) in Col A exists in Col B or not.

How can I compare and identify?

PS: I am trying to join two tables in access but it won't because the Parent - Child relationship has gone sour !

Please advise.

Thanks,
SG

[twinnyfo]

I would create two recordsets in VBA, cycle through the first recordset, and using the current value of the record, cycle through the second recordset. You may want to determine a way to save your results or print the records that are not found.

Then, determine what you want to do with your orphaned records...

[sg2808]

Thanks twinnyfo but I am absolutely zero with VBA. Any help would be greatly appreciated.

[twinnyfo]

If you are slick enough with queries, you could create a LEFT JOIN query that will display all records from one table whether there is a corresponding record inthe other table. Then, set as the criteria for the record in table2 as Null.

Assuming you have Table1 and Table2 in the example you gave with said fields, this is what it should look like:

SELECT Table1.ColumnA
FROM Table1 LEFT JOIN Table2 ON Table1.ColumnA = Table2.ColumnB
WHERE Table2.ColumnB Is Null;
 

In your example above, this would return the value of "3", since it is the only value in ColumnA not found in ColumnB.

Hope this helps.

[zmbd]

sg2808,

Twinnyfo was really nice to help; however, normally you should really post what you've already tried to solve the problem.
http://bytes.com/topic/access/answer...-posting-forum

As you already have one answer... this uses a self join on the one table... same concept as Twinnyfo just no extra tables to create.

Table3 has Fields [f1] and [f2] as numeric using the data as given in OP. Checks [F1] against [F2].

I leave it to you tweek the table/SQL to add a record id.

SELECT Table3.f1
FROM Table3 
   LEFT JOIN Table3 AS Table3_Again 
      ON Table3.f1 = Table3_Again.f2
WHERE (((Table3_Again.f2) Is Null));
 

-z