can ACCESS execute SQL like "UNION CORRESPONDING"

[amolin]

can ACCESS execute SQL like "UNION CORRESPONDING", I need this type SQL to make my application more flexible, but I found ACCESS doesn't recognize it!
what other method should i do?

 

[krunalprajapati]

Union Corresponding??????

I doubt, is there a function like UNION CORRESPONDING in SQL???????

if it is then explain it to me.

if u want to use union or union all it is possible in access.

 

[amolin]

Thank you, I detail it as following..

Table 1

fieldName1 ----fieldName2---- fieldName3---fieldName4

Table 2

fieldName2---- fieldName3---- fieldName4---fieldName5


I need Union corresponding fields into new table... but following SQL failed

Select * from Table1
UNION
Select * from Table2

can i have another way other than list all fields in SQL?

 

[krunalprajapati]

all field's name in both table are same, if not than it wan't work.
if the fields name r not same than use join insted of union.

 

[amolin]

all field's name in both table are same, if not than it wan't work.
if the fields name r not same than use join insted of union.

Table 1

fieldName1 ----<fieldName2>---- <fieldName3>---<fieldName4>-------------
Table 2

--------------<fieldName2>---- <fieldName3>---<fieldName4>---fieldName5


Could you kindly tell me how to use "join" to Union two tables which have fields like above? the new table should have like following

--------------<fieldName2>---- <fieldName3>---<fieldName4>--------------

 

[krunalprajapati]

ok this may ur answer. open the link below

 

[amolin]

Thank you! but , to make it more flexible,I don't want to list the fields name in SQL. I just need to union the corresponding fields of two tables, do you have any idea?

 

[krunalprajapati]

union the corresponding fields of two table

I didn't understand ur que. what do u mean by "union the corresponding fields of two table" ? it means u want all the fields from t1 and t2 table? or u want to create a new table with common fields??????

explain ur question by giving field's name and data of both tables.

 

[amolin]

Table 1

fieldName1 ----<fieldName2>---- <fieldName3>---<fieldName4>-------------
Table 2

--------------<fieldName2>---- <fieldName3>---<fieldName4>---fieldName5

I want to get result as following by Query(don't list fields name in query):

--------------<fieldName2>---- <fieldName3>---<fieldName4>--------------

Thank you again..

 

[Pat Hartman]

Thank you! but , to make it more flexible,I don't want to list the fields name in SQL. I just need to union the corresponding fields of two tables, do you have any idea?

There is no union corresponding. Union doesn't depend on column names, it depends on ordinal position and data type. Column 1 of the first table is unioned with column 1 of the second table, etc.

If your tables do not contain columns in the same order or of the same data type or you don't want to include all columns in the recordset, union queries rather than tables. The queries can order the columns appropriately and change their data types if necessary.

Your alternative is to write your own VBA procedure to create the SQL to do the union "your" way. You'll need to pass the procedure some number of tables or queries and then use arrays to loop through the column names to find the matching ones.

 

[amolin]

There is no union corresponding. Union doesn't depend on column names, it depends on ordinal position and data type. Column 1 of the first table is unioned with column 1 of the second table, etc.

If your tables do not contain columns in the same order or of the same data type or you don't want to include all columns in the recordset, union queries rather than tables. The queries can order the columns appropriately and change their data types if necessary.

Your alternative is to write your own VBA procedure to create the SQL to do the union "your" way. You'll need to pass the procedure some number of tables or queries and then use arrays to loop through the column names to find the matching ones.

Thank you, Pat Hartman:

It seems I got your idea.

Do you mean i need write a procedure to find the same field names of two tables, then dynamicly generate a SQL string by concatenating those common fields?

 

[Pat Hartman]

You'll need the arrays because you'll need to sort the column names to facilitate finding matching columns.