By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to compare and identify data in array?

P: 91
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
Aug 8 '12 #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,034
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...
Aug 8 '12 #2

P: 91
Thanks twinnyfo but I am absolutely zero with VBA. Any help would be greatly appreciated.
Aug 8 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,034
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ColumnA
  2. FROM Table1 LEFT JOIN Table2 ON Table1.ColumnA = Table2.ColumnB
  3. WHERE Table2.ColumnB Is Null;
  4.  
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.
Aug 8 '12 #4

zmbd
Expert Mod 5K+
P: 5,286
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table3.f1
  2. FROM Table3 
  3.    LEFT JOIN Table3 AS Table3_Again 
  4.       ON Table3.f1 = Table3_Again.f2
  5. WHERE (((Table3_Again.f2) Is Null));
  6.  
-z
Aug 8 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.