Join two separate databases using common fields slightly different in each database

[Epigenetic_Dave]

I am trying to form a join between two separate databases. Database 1 has a column (Gene_Name2) that consists of common names of human genes. In this database there is only one name for each gene per field. The other Database 2 has a column (Gene_Name2) that has various names for the same gene in each field. Most genes have been given different names over time. I want to join the databases into a separate query database so that all of the information in Database 1 is retained and only when the name in Database 1 matches one of the names in Database 2 does the row of information get tacked on to the query database. The problem is that in a normal query the text in both Gene_Name columns do not match and the query returns no matches. I have tried Instr and like criteria statements with no luck. Can anybody help. The following is an example of what I have and what I need. Thanks




Database 1


Chrom Gene_Name1 Data1
Chr1 ATC XYZ
Chr2 IDH2 ZYZ

Database 2


Gene_Name2 Data2

ATC;ORF34;NM_123 ABC

ORF65;IDH2 IGH

Joined Query

Chrom Gene_Name1 Data1 Gene_Name2 Data2

Chr1 ATC XYZ ATC;ORF34;NM_123 ABC

Chr2 IDH2 ZYZ ORF65;IDH2 IGH

[RuralGuy]

Are you talking about 2 separate accdb files or simply two different TABLES in the same Database (accdb file)?

[Epigenetic_Dave]

RuralGuy,

I imported two separate tab delimited text files into Access so I guess it is two different TABLES in the same Database

Dave

[RuralGuy]

What is Chr1 and Chr2? Are all you data in one field?

[Epigenetic_Dave]

Chr1 and chr2 are the chromosome number. The data is in separate fields except Gene_Name2. The problem is that in Table1, Gene_Names1 is something like ATC, where Table2 Gene_Names2 consists of a name like ATC;ORF34;NM_35. If you try to do a query and used the selection criteria as ATC then Access does not allow for the recognition of ATC in ATC;ORF34;NM_35.

Dave

[RuralGuy]

Have you looked at the InStr() function?

[RuralGuy]

I'm sorry but I have to leave for several hours. I'll check back in when I get back.

[Epigenetic_Dave]

RuralGuy-I was able to solve my problem with the following. Thanks for your efforts.


SELECT Table1.Chrom, Table1.Gene_Name1, Table1.Data, Table2.Gene_Name2, Table2.Data
FROM Table1, Table2
WHERE (((Table2.Gene_Name2) Like "*" & [Table1].[Gene_Name1] & "*"));

[RuralGuy]

Outstanding! Are you ready to use the Thread Tools at the top of the thread and mark this thread as Solved?