Append Query: Only append 1 field

[Flank]

I am trying without any success writing an append query in my database.

I have done it many times before but not with such a limited scope.

I have a main table called zEMPMain. This is the main data storage.

I have another table called CICMH. This is the table that I want to append new data to.

In the zEMPMain table there are multiple fields but I only want to append the data from one field called MHIC.

In the CICMH table there are also multiple fields.

I only want to append the MHIC data from zEMPMain to the CICMHTotal in the CICMH table.

When I set up the the query the SQL looks like this

INSERT INTO CICMH ( CICMHTotal )
SELECT zEMPMain.MHIC, *
FROM zEMPMain INNER JOIN CICMH ON zEMPMain.ID = CICMH.ID;

I then get an error that says Insert INTO statement contains the following unknown field name: 'Project Name'.

That is a field in the zEMPMain table but I don't want it to append to the CICMH table.

 

[boblarson]

Get rid of this part:

, *

 

[Flank]

Hmmm lol ok that was easy. Can't believe I didn't see that.

Thank you for your help.

It ran just as expected, but proposes another problem.

It didn't create any records.

Currently there is 1 record in the zEMPMain table and 0 records in the CICMH table.

They both have their primary key as ID and are linked in the query. And both the ID's are AutoNumber fields. Could this be the problem?

 

[boblarson]

Remove this part:

INNER JOIN CICMH ON zEMPMain.ID = CICMH.ID

If you are appending, the ID field in CICHMH isn't going to exist yet.

 

[Flank]

Excellent thank you very much that works perfectly.