how to INSERT unmatch query

[ges via AccessMonster.com]

I have two tables using AcctIDAuto to identify the accounts. When I import
excel file, new AcctIDAuto being added to one table (tblCollectionsInfo). I
try to append new added AcctIDAuto that's in tblCollectionsInfo into
tblCollectionNotes. My syntax as follow:

INSERT INTO tblCollectorNotes ( AcctIDAuto, )
SELECT tblCollectionsInfo.AcctIDAuto, tblCollectorNotes.AcctIDAuto
FROM tblCollectionsInfo LEFT JOIN tblCollectorNotes ON tblCollectionsInfo.
AcctIDAuto = tblCollectorNotes.AcctIDAuto
WHERE (((tblCollectorNotes.AcctIDAuto) Is Null));

Before import, tblCollections info and tblCollectorNotes has 15,000 records.
After import tblCollectionsInfo has 25,000 records and I try to add 10,000
new records that was being added to tblCollectionsInfo into
tblCollectionsNotes.
The above syntax give me an error saying the "number of query value and
destination field is not match".

Can anybody help me with the syntax? THANK YOU in advance for any help.

Ges

 

[karl dewey]

Try this --
INSERT INTO tblCollectorNotes ( AcctIDAuto )
SELECT tblCollectionsInfo.AcctIDAuto
FROM tblCollectionsInfo LEFT JOIN tblCollectorNotes ON
tblCollectionsInfo.AcctIDAuto = tblCollectorNotes.AcctIDAuto
WHERE (((tblCollectorNotes.AcctIDAuto) Is Null));

 

[MGFoster]

I have two tables using AcctIDAuto to identify the accounts. When I import
excel file, new AcctIDAuto being added to one table (tblCollectionsInfo). I
try to append new added AcctIDAuto that's in tblCollectionsInfo into
tblCollectionNotes. My syntax as follow:

INSERT INTO tblCollectorNotes ( AcctIDAuto, )
SELECT tblCollectionsInfo.AcctIDAuto, tblCollectorNotes.AcctIDAuto
FROM tblCollectionsInfo LEFT JOIN tblCollectorNotes ON tblCollectionsInfo.
AcctIDAuto = tblCollectorNotes.AcctIDAuto
WHERE (((tblCollectorNotes.AcctIDAuto) Is Null));

Before import, tblCollections info and tblCollectorNotes has 15,000 records.
After import tblCollectionsInfo has 25,000 records and I try to add 10,000
new records that was being added to tblCollectionsInfo into
tblCollectionsNotes.
The above syntax give me an error saying the "number of query value and
destination field is not match".

Can anybody help me with the syntax? THANK YOU in advance for any help.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have two columns in the SELECT clause but only one column in the
INSERT INTO (...) clause - they both have to have the same number of
columns of the same data type. Plus, your JOIN is really useless 'cuz
there isn't any AcctIDAuto in the tblCollectorNotes to match the
tblCollictionsInfo.AcctIDAuto. Do you already have notes in the
tblCollectorNotes?!

There should be a note to ADD to the tblCollectorNotes shouldn't there?
If not, wait until you have a note to add to the table before trying to
append the row (record). This probably would be better done thru a sub
form. The main form would be based on the tblCollectionsInfo and linked
thru the AcctIDAuto. The sub-form will automatically get the correct
AcctIDAuto value when a note is entered into the sub-form.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSac/2oechKqOuFEgEQLc/wCg9KaZNTtguEcvxiwCJTi2UIyDmGYAoLfC
/yEJ/8LhESnjQ5t3MTMHV4Dx
=CXVW
-----END PGP SIGNATURE-----

 

[ges via AccessMonster.com]

Yes I notice that. I take out the column (AcctIDAuto) selected twice in
SELECT statement. It still give me error. Yes there is note in
tblCollectorNotes(12 fields). I'm using AcctIDAuto to link to the data from
tblCollectionsInfo to tblCollectorNotes. So when I import new data I try to
add the new added AcctIDAuto in tblCollectionsInfo to tblCollectorNotes. I'm
using a form to bring the data in both tables so user can type in the notes
and update the fields in tblCollectorNotes and tblCollectionsInfo fields.
Noted the join right now is useless because of tlbCollectionsInfo has a lot
more data then tblCollectorNotes. (but I need the join in order to link data
between two tables)
Is there any syntax that can help me add new additional data into missing
data in tblcollectorNotes?

Thanks

I have two tables using AcctIDAuto to identify the accounts. When I import
excel file, new AcctIDAuto being added to one table (tblCollectionsInfo). I

[quoted text clipped - 15 lines]

Can anybody help me with the syntax? THANK YOU in advance for any help.

You have two columns in the SELECT clause but only one column in the
INSERT INTO (...) clause - they both have to have the same number of
columns of the same data type. Plus, your JOIN is really useless 'cuz
there isn't any AcctIDAuto in the tblCollectorNotes to match the
tblCollictionsInfo.AcctIDAuto. Do you already have notes in the
tblCollectorNotes?!

There should be a note to ADD to the tblCollectorNotes shouldn't there?
If not, wait until you have a note to add to the table before trying to
append the row (record). This probably would be better done thru a sub
form. The main form would be based on the tblCollectionsInfo and linked
thru the AcctIDAuto. The sub-form will automatically get the correct
AcctIDAuto value when a note is entered into the sub-form.

 

[ges via AccessMonster.com]

Disregard my post, I get the syntax working already, thanks.