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

How to Query Partial Match from Two Tables

P: 2
Relative Newbie w/ a stumper! Any help is appreciated. Thanks!

I have two tables that I have imported to access. I'd like to query between these two tables for records that have a shared last name (under a name field) in both tables.

It's a bit complicated because the name fields in the two tables are formatted differently, and sometimes inconsistently. For example: "ROGER D & BETTY JO COLE" in table 1, becomes "COLE ROGER D" in table 2.

I've seen lots of good information about joining the tables, but that seems more difficult here (I'd need to split up the name field data --difficult with middle initials and two part names [eg., Mary Jo] and then find a way to join the appropriate pieces).

Is there a better way to search for matches (or, possible matches), such as using a wild card search (eg., Like *COLE*) that would work on a whole sale basis?

Jul 13 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,356
Personally in this situation I would think that you could use a union query then you could search both tables for the information and start working on a cleanup solution.

This would have to be done in SQL view of a query, of course and would look something like:

Select [last name], "Table1" as Table1 From [Table one]
Union Select [last name], "Table2" as Table2 From [Table Two]
Jul 13 '10 #2

P: 2
Thanks for the suggestion, Denburt. I'm not very familiar with SQL, so I'll have to do some googling before I know how well that will work --- again, one complication is that the name fields aren't neatly separated into first, middle, and last name fields.

I may be totally off-base here, but any idea why this doesn't work:

SELECT Table1.*, Table2.*, Table1.FullNameTb1
FROM Table1, Table2
WHERE (((Table1.FullNameTb1) Like "*[Table2].[FullNameTb2]*"));

Thanks again.
Jul 13 '10 #3

Expert 100+
P: 1,356
@Pat Rowe
Hmm that's an interesting approach. You can probably use that although you would need a slight adjustment.
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*, Table2.*, Table1.FullNameTb1
  2. FROM Table1, Table2
  3. WHERE (((Table1.FullNameTb1) Like "*" & [Table2].[FullNameTb2] & "*"));
The way you posted it you were looking for the text [Table2].[FullNameTb2] in the actual field and not the data in [Table2].[FullNameTb2].
Jul 13 '10 #4

P: 1
Thanks - that's a perfect solution to a look up I was trying to do. I just got rid of the need for about 50 individual 'like' queries
Feb 3 '12 #5

Post your reply

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