SQL Query to JOIN on field renames field in Access VBA -

i have access vba program joins 2 tables , stores them in one. issue instead of sql query joining on commonfield , having new table commonfield field, new table has commonfield named tablename1_commonfield , named tablename2_commonfield.

so, if query following:

select iipm.*, unix.* [unix_lob] iipm inner join unix on iipm.[appcode_0] =  unix.[appcode_0]; 

then appcode_0 field common between iipm , unix becomes 2 fields named iipm_appcode_0 , unix_appcode_o in new table unix_lob when want remain single field appcode_0.

i have traced right after sql query executed:

' set name of new table joins stored. ' newtablename = "[" + tablename2 + "_lob]"  ' join tablename1 , tablename2 on commonfield, , store joined data in newtablename ' sqljoinquery = "select " + tablename1 + ".*, " + tablename2 + ".*" & _              " " + newtablename & _              " " + tablename1 & _              " inner join " + tablename2 & _              " on " + tablename1 + "." + commonfield + " = " + tablename2 + "." + commonfield + ";" debug.print sqljoinquery currentdb.execute sqljoinquery 

after writing comment above, saw other question, thought expand on said above bit:

create table jointable (table1_key long null, table1_somecolumn text(50) null, table2_key long null, table2_somecolumn text(50) null); 

then having done this,

insert jointable (table1_key, table1_somecolumn, table2_key, table2_somecolumn) select t1.key, t1.somecolumn, t2.key, t2.somecolumn table1 t1 inner join table2 t2 on t1.key = t2.key 

and should you're after (albeit no doubt queries bit messier these!)

hope helps