Combination of data field to third Table

[amit_m04]

Hi,

i want to combine the data fields from two different tables into third table.

Eg:

Table1
----------
ID | Name
----------
T1 | A
|
|


Table2
------------
ID | Name
------------
T_1 | XY
T_2 | CB
|


Table3
----------------
ID | Name
----------------
T100 | A
T1T_1 | A & XY
T1T_2 | A & CB
T_100 | XY
T_200 | CB

whenever there is any entry into any of 1st and 2nd table then that entry should be done into the respective ( 1st/2nd) table's and also it should be entered into the third table along with all the possible combinations with each other into the third table...

i want to do this using vb.net and ms access database...

pls help me on this...

 

[cjard]

The only way I can think of achieving something like that is by a query like this:

SELECT t1.ID & t2.ID as ID, t1.Name & ' & ' & t2.Name FROM t1, t2
UNION ALL
SELECT t1.ID & '00', t1.Name FROM t1
UNION ALL
SELECT t2.ID & '00', t2.Name FROM t2

Where t1 and t2 are the names of your tables

 

[amit_m04]

Hi Cjard,

first of all thankyou for your reply..

but how can i add the combination to third table (Table3)..?

and if possible can u plz explain the query to me..

 

[cjard]

INSERT INTO table3
SELECT t1.ID & t2.ID as ID, t1.Name & ' & ' & t2.Name FROM t1, t2
UNION ALL
SELECT t1.ID & '00', t1.Name FROM t1
UNION ALL
SELECT t2.ID & '00', t2.Name FROM t2

or if it doesnt exist

CREATE TABLE table3 AS
SELECT t1.ID & t2.ID as ID, t1.Name & ' & ' & t2.Name FROM t1, t2
UNION ALL
SELECT t1.ID & '00', t1.Name FROM t1
UNION ALL
SELECT t2.ID & '00', t2.Name FROM t2

or

SELECT t1.ID & t2.ID as ID, t1.Name & ' & ' & t2.Name FROM t1, t2
UNION ALL
SELECT t1.ID & '00', t1.Name FROM t1
UNION ALL
SELECT t2.ID & '00', t2.Name FROM t2
INTO table3