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

Comparing 2 tables letter by letter in Access

P: 2
I am trying to compare 2 tables and trying to find out the common ones in that.
The example goes like this.

Expand|Select|Wrap|Line Numbers
  1. Table 1            Table 2
  2. Company  Type      Company            Address
  3. ABC      Partner   ABC UK Ltd.        123, UK
  4. XYZ      Customer  ABC UK Pvt. Ltd.   123,UK
  5. DEF      Partner   ABC USA Pvt.       456,USA
Now when I compare these two tables , the result should be this
Expand|Select|Wrap|Line Numbers
  1. Table 3
  2. Company            Address    Type
  3. ABC UK Ltd.        123, UK    Partner
  4. ABC UK Pvt. Ltd.   123,UK     Partner
  5. ABC USA Pvt.       456,USA    Partner
It means lt should compare ABC with ABC UK Ltd.,ABC UK Pvt. Ltd. etc.

I tried using for and like but did not get the result.

Please help
Feb 20 '07 #1
Share this Question
Share on Google+
4 Replies


P: 2
I am trying to compare 2 tables and trying to find out the common ones in that.
The example goes like this.

Table 1 Table 2
Company Type Company Address
ABC Partner ABC UK Ltd. 123, UK
XYZ Customer ABC UK Pvt. Ltd. 123,UK
DEF Partner ABC USA Pvt. 456,USA

Now when I compare these two tables , the result should be this
Table 3
Company Address Type
ABC UK Ltd. 123, UK Partner
ABC UK Pvt. Ltd. 123,UK Partner
ABC USA Pvt. 456,USA Partner

It means lt should compare ABC with ABC UK Ltd.,ABC UK Pvt. Ltd. etc.

I tried using for and like but did not get the result.

Please help

Can anyone help please
Feb 20 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
You could try this but I can't guarantee 100% accurate results.

Expand|Select|Wrap|Line Numbers
  1. SELECT Company, Address, 
  2. DLookup("[Type]", "Table1", "[Company] LIKE " & Left([Company], InStr([Company], " ")-1) & "*" As Type
  3. FROM Table2;
Mary
Feb 20 '07 #3

ADezii
Expert 5K+
P: 8,599
I am trying to compare 2 tables and trying to find out the common ones in that.
The example goes like this.

Table 1 Table 2
Company Type Company Address
ABC Partner ABC UK Ltd. 123, UK
XYZ Customer ABC UK Pvt. Ltd. 123,UK
DEF Partner ABC USA Pvt. 456,USA

Now when I compare these two tables , the result should be this
Table 3
Company Address Type
ABC UK Ltd. 123, UK Partner
ABC UK Pvt. Ltd. 123,UK Partner
ABC USA Pvt. 456,USA Partner

It means lt should compare ABC with ABC UK Ltd.,ABC UK Pvt. Ltd. etc.

I tried using for and like but did not get the result.

Please help
The following code will produce the results that you are loooking for. It will compare every Record in Table1 agains every Record in Table2. If the [Company] Name in Table1 is a partial match (contained within) to the [Company] Name in Table2, it then writes the appropriate values to Table3. Is this, in fact, what you were looking for?
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rsTable1 As DAO.Recordset, rsTable2 As DAO.Recordset
  2. Dim rsTable3 As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rsTable1 = MyDB.OpenRecordset("Table1", dbOpenDynaset)
  6. Set rsTable2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)
  7. Set rsTable3 = MyDB.OpenRecordset("Table3", dbOpenDynaset)
  8.  
  9. rsTable1.MoveFirst
  10. rsTable2.MoveFirst
  11. Do While Not rsTable1.EOF
  12.   Do While Not rsTable2.EOF
  13.     If InStr(rsTable2![Company], rsTable1![Company]) > 0 Then
  14.       'MsgBox rsTable1!Company & " <==> " & rsTable2!Company
  15.       rsTable3.AddNew
  16.         rsTable3![Company] = rsTable2![Company]
  17.         rsTable3![Address] = rsTable2![Address]
  18.         rsTable3![Type] = rsTable1![Type]
  19.       rsTable3.Update
  20.     End If
  21.     rsTable2.MoveNext
  22.   Loop
  23.   rsTable2.MoveFirst
  24.   rsTable1.MoveNext
  25. Loop
  26.  
  27. rsTable1.Close
  28. rsTable2.Close
  29. rsTable3.Close
Feb 20 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
Can anyone help please
Please do not post impatient prompts on here.
Our experts are very valued and volunteers and it is not reasonable to expect an immediate response for a post on here, being as it's a web site and not your personal support team.
Bumping of threads is acceptable only if you find that a response is not forthcoming after at least 24 hours.

MODERATOR.
Feb 22 '07 #5

Post your reply

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