sql - Creating a table with data from 2 other tables -

i working in access , utilizing vba make queries in wich have variable names, can't use query wizard in access

i have following 2 tables:

tblkabelinfo 

and table name varies depending on else in program

tblname1 string 

the tables this:

tblkabelinfo:  +--------------+----------+----------+----------+ | kabelnummer  |   data   |more data |even more |            +--------------+----------+----------+----------+ | 1            |    x     |    x     |    x     | +--------------+----------+----------+----------+ | 2            |    x     |    x     |    x     | +--------------+----------+----------+----------+ | 3            |    x     |    x     |    x     | +--------------+----------+----------+----------+ | 4            |    x     |    x     |    x     | +--------------+----------+----------+----------+  tblname1:  +--------------------------------+----------+----------+ | filename                       |   bla    | databla  | +--------------------------------+---------------------+ |\850\850nm_lessenaar 1_0001.sor |    x     |    x     | +--------------------------------+----------+----------+ |\850\850nm_lessenaar 1_0002.sor |    x     |    x     | +--------------------------------+----------+----------+ |\850\850nm_lessenaar 1_0003.sor |    x     |    x     | +--------------------------------+----------+----------+ |\850\850nm_lessenaar 1_0004.sor |    x     |    x     | +--------------------------------+----------+----------+ 

i know both tables of same size (so if table "tblname1" goes 0234.sor, know kabelnummer "tblkabelinfo" goes 234)

i make query makes new table looks this:

newtable:  +--------------------------------+--------------+-----+--------+-----------+---------+ | filename                       |kabelnummer   | bla |databla | more data |even more| +--------------------------------+--------------+--------------+-----------+---------+ |\850\850nm_lessenaar 1_0001.sor | 1            |  x  |   x    |     x     |    x    | +--------------------------------+--------------+-----+--------+-----------+---------+ |\850\850nm_lessenaar 1_0002.sor | 2            |  x  |   x    |     x     |    x    | +--------------------------------+--------------+-----+--------+-----------+---------+ |\850\850nm_lessenaar 1_0003.sor | 3            |  x  |   x    |     x     |    x    | +--------------------------------+--------------+-----+--------+-----------+---------+ |\850\850nm_lessenaar 1_0004.sor | 4            |  x  |   x    |     x     |    x    | +--------------------------------+--------------+-----+--------+-----------+---------+ 

i have 2 tables in 1 table , common factor end of "filename" should same "kabelnummer"

it seems basic challenge here identify digits in tblname1.filename can used join tblkabelinfo.kabelnummer.

if digits first 4 of last 8 characters of string, can use right , left, compatible access sql, them easily.

here session immediate window.

filename = "\850\850nm_lessenaar 1_0001.sor" ? right(filename, 8) 0001.sor ? left(right(filename, 8), 4) 0001 

if need convert characters numeric value, can use val function.

? val(left(right(filename, 8), 4))  1  

however, if filename values more variable, not ending period , 3 more characters, task more challenging.

filename = "\850\850nm_lessenaar 1_0001.abcdef" ? instrrev(filename, "_")  23  ? instrrev(filename, ".")  28  ? mid(filename, instrrev(filename, "_") + 1, _     (instrrev(filename, ".") - instrrev(filename, "_")) - 1) 0001 ? val(mid(filename, instrrev(filename, "_") + 1, _     (instrrev(filename, ".") - instrrev(filename, "_")) - 1))  1  

once work out appropriate mix of functions need, can use them in access query. here query using both of approaches. runs without error in access 2007 sample data in tblname1.

select     t.filename,     val(left(right(filename, 8), 4)) kabelnummer1,     val(             mid(                 filename,                 instrrev(filename, "_") + 1,                 (instrrev(filename, ".") - instrrev(filename, "_")) - 1             )         ) kabelnummer2 tblname1 t;