'Number of query values and destination fields are not same' error

[ralamo]

When i execute the following insert query, the above mentioned error
is coming. Anything wrong in the query?
INSERT INTO ECN_1 (Old_SbPartNo, Old_PartDesc, Old_ManPartNo,
Old_Manuf, Old_Vendor, Old_RefDesi, Old_Qty,
New_SbPartNo, New_PartDesc, New_ManPartNo,N ew_Manuf, New_Vendor,
New_RefDesi, New_Qty)
select * from (select SB0014.SbPartNo , SB0014.partDesc ,
SB0014.mANpARTn O, SB0014.manufact urer,
SB0014.vendor, sb0014.refdesi, sb0014.qty,
SB0014_2.SbPart No, SB0014_2.partDe sc, SB0014_2.mANpAR TnO,
SB0014_2.manufa cturer, SB0014_2.vendor ,
sb0014_2.refdes i, sb0014_2.qty
from SB0014 LEFT OUTER JOIN SB0014_2
on SB0014.SBPARTNO = SB0014_2.SBPART NO
where sb0014_2.SbPart No is null
or sb0014.partDesc <> sb0014_2.partde sc
or sb0014.manpartn o <> sb0014_2.manpar tno
or sb0014.manufact urer <> sb0014_2.manufa cturer
or sb0014.vendor <> sb0014_2.vendor
or sb0014.refdesi <> sb0014_2.refdes i
or sb0014.qty <> sb0014_2.qty
union all
select null, null, null, null, null, null,null,
sb0014_2.SbPart No, SB0014_2.partDe sc, SB0014_2.mANpAR TnO,
SB0014_2.manufa cturer,
SB0014_2.vendor , sb0014_2.refdes i, sb0014_2.qty
from sb0014
right outer join sb0014_2 on sb0014.SbPartNo = sb0014_2.SbPart No
where sb0014.SbPartNo is null) as u

When i execute it, it is giving me following error.

"java.sql.SQLEx ception: [Microsoft][ODBC Microsoft Access Driver]
Number of query v
alues and destination fields are not the same."

I checked the number of fields in table ECN_1 and query values which
seems to be same ie. 14. Pls let me know if there is anything wrong in
the above query.

[dmkeith2]

I think it would be easier for you to create your union query first and
then create your insert into query. My code for one that works for me
is below, you definately want your fields to match up otherwise you'll
get an error, they don't have to be the same name but they have to
match up column wise.

My Insert Into Code (this is inserting using my union query)

INSERT INTO test ( ParaLine, [Position], [Section], MOSAOC, ASI1, ASI2,
Grade, Auth, MaleFemale )
SELECT qryMTOE.ParaLin e, qryMTOE.Positio n, qryMTOE.Section ,
qryMTOE.MosAoc, qryMTOE.ASI1, qryMTOE.ASI2, qryMTOE.Grade,
qryMTOE.Auth, qryMTOE.MaleFem ale
FROM qryMTOE;

My Union Query Code:

SELECT qryMTOEenlisted .ParaLine, qryMTOEenlisted .Position,
qryMTOEenlisted .Section, qryMTOEenlisted .MosAoc, qryMTOEenlisted .ASI1,
qryMTOEenlisted .ASI2, qryMTOEenlisted .Grade, qryMTOEenlisted .Auth,
qryMTOEenlisted .MaleFemale
FROM qryMTOEenlisted ;
UNION

SELECT qryMTOEofficer. ParaLine, qryMTOEofficer. Position,
qryMTOEofficer. Section, qryMTOEofficer. MosAoc, qryMTOEofficer. ASI1,
qryMTOEofficer. ASI2, qryMTOEofficer. Grade, qryMTOEofficer. Auth,
qryMTOEofficer. MaleFemale
FROM qryMTOEofficer

UNION SELECT qryMTOEwarrant. ParaLine, qryMTOEwarrant. Position,
qryMTOEwarrant. Section, qryMTOEwarrant. MosAoc, qryMTOEwarrant. ASI1,
qryMTOEwarrant. ASI2, qryMTOEwarrant. Grade, qryMTOEwarrant. Auth,
qryMTOEwarrant. MaleFemale
FROM qryMTOEwarrant;

Notice how everything lines up with all my columns, make sure yours
does. I'm not an expert I just know how to get a lot of it to work.

[ralamo]

Thank you for the answer. I eliminated 'select * ' after Insert
statement and now that error is gone. But facing java.sql.SQLExc eption
: General error

Don't know how to fix. I don't know how to run this query in Ms Access
2000. Else i might have first checked there whether it's working or not
and then included in my JSP code.

My query looks like this.

INSERT INTO ECN_1 (Old_SbPartNo, Old_PartDesc, Old_ManPartNo,
Old_Manuf, Old_Vendor, Old_RefDesi, Old_Qty,
New_SbPartNo, New_PartDesc, New_ManPartNo,N ew_Manuf, New_Vendor,
New_RefDesi, New_Qty)

select SB0014.SbPartNo , SB0014.partDesc , SB0014.mANpARTn O,
SB0014.manufact urer, SB0014.vendor, sb0014.refdesi, sb0014.qty,
SB0014_2.SbPart No, SB0014_2.partDe sc, SB0014_2.mANpAR TnO,
SB0014_2.manufa cturer, SB0014_2.vendor , sb0014_2.refdes i, sb0014_2.qty
from SB0014 LEFT OUTER JOIN SB0014_2
on SB0014.SBPARTNO = SB0014_2.SBPART NO
where sb0014_2.SbPart No is null
or sb0014.partDesc <> sb0014_2.partde sc
or sb0014.manpartn o <> sb0014_2.manpar tno
or sb0014.manufact urer <> sb0014_2.manufa cturer
or sb0014.vendor <> sb0014_2.vendor
or sb0014.refdesi <> sb0014_2.refdes i
or sb0014.qty <> sb0014_2.qty

union all

select null, null, null, null, null, null,null,
sb0014_2.SbPart No, SB0014_2.partDe sc, SB0014_2.mANpAR TnO,
SB0014_2.manufa cturer, SB0014_2.vendor , sb0014_2.refdes i, sb0014_2.qty

from sb0014
right outer join sb0014_2
on sb0014.SbPartNo = sb0014_2.SbPart No
where sb0014.SbPartNo is null