the error i get from visual studio is Number of query values and destination fields are not the same.
below is my insert statement

"INSERT INTO tbl_ViewAll(EquipTag, S_Name, S_Function, S_Failure, MC_No, MC_Name, MC_Function)" &
            "SELECT EquipTag,S_Name,S_Function,S_Failure FROM tbl_System UNION " &
          "SELECT MC_No,MC_Name,MC_Function FROM tbl_MainComp"

Hello,

Not sure what you are attempting to do but the two selects that are trying to use UNION are defiantly wrong. UNION is used to put to sets of like results together, meaning that the selects have the same number of result fields with the same names.

SELECT Name, Address, City, State, Zip from table1
UNION
SELECT Name, Address, City, State, Zip from table2

You have 4 fields in the first and 3 in the second and they do not have matching names.

If you will telll us what your trying to do we can probably steer you in the right direction.

To copy data from one table to another you must have to sure that the fields structure of those tables are same like fields name and data type . Then the syntax is

Insert Into My_Table1_Name Select * From My_Table2_Name

But if you want to insert some selective data then you have to add where clause with it.

Simply, if you try to select all fields from two diferent table then the syntax is

Select * From Table1, Table2

For some selective fields

Select A.field1, A.field2, A.field3, B.field1, B.field2 From Table1 A, Table2 B

To select some selective data use join and where clause.

This article has been dead for over six months. Start a new discussion instead.