Help with append query please!!

[doomik]

Hello all, I have 2 tables I want to join. I want to merge them on 2 fields:
tblHVAC.[Site ID] = tblHVACaddidata.[Site ID]
tblHVAC.Item = tblHVACaddidata.Item

Below is my SQL code:

INSERT INTO tblHVAC
SELECT tblHVACaddidata.*
FROM tblHVAC LEFT JOIN tblHVACaddidata ON (tblHVAC.[Item] = tblHVACaddidata.[Item]) AND (tblHVAC.[Site ID] = tblHVACaddidata.[Site ID]);

And I get this error:
The INSERT INTO statement contains the following unknown field name: 'District'. Make sure you have typed the name correctly, and try the operation again.

Now, my SQL statement doesn't say anything about the field name 'Distric' so I can't figure out why I am getting this error.

Please help!!

Thanks in advance!

 

[doomik]

I changed the query to a select query:
SELECT tblHVACaddidata.*
FROM tblHVAC LEFT JOIN tblHVACaddidata ON (tblHVAC.[Item] = tblHVACaddidata.[Item]) AND (tblHVAC.[Site ID] = tblHVACaddidata.[Site ID]);

And had no problem running the query.

There's no reference to the district field in the SQL or the design view, so I have no idea why this is happening! There's a field called district in the table tblHVACaddidata...but I want to take the fields (and data) from the HVACaddidata and put them into the tblHVAC based on Site ID and Item.

Does that additional information help? I am stumped!

Thanks!!

 

[CJ_London]

you have said

There's a field called district in the table tblHVACaddidata

for this to work in your inset query

INSERT INTO tblHVAC
SELECT tblHVACaddidata.*

tblHVAC must have exactly the same field names and types as tblHVACaddidata

If it doesn't you need a more detailed query

INSERT INTO tblHVAC (field1, field2, field3....)
SELECT tblHVACaddidata.field1, tblHVACaddidata.field2, tblHVACaddidata.field3...

 

[doomik]

Well, I tried it both ways...and I even added the fields to the tblHVAC (the table I am trying to add data to). It didn't merge the tables but instead just added all the data from the second table (tblHVACaddidata) to the end of the first table...which is not what I need.
What am I missing?

 

[CJ_London]

What am I missing?

Hard to know without knowing exactly what you are trying to do. Perhaps you need a criteria to select which records are to be inserted? or perhaps it needs to be an inner join rather than an outer join?

 

[plog]

I think its time for sample data to demonstrate what you hope to accomplish. We will need 3 sets of data:

A. Sample data from tblHVAC before the query runs.

B. Sample data from tblHVACaddidata.

C. Sample data from tblHVAC after the query runs.

Please provide table and field names. Also, think about your sample data carefully: provide enough records to demonstrate all cases.

 

[doomik]

Here's some sample data...the workbook has 3 tabs corresponding to the tblHVAC, tblHVACaddidata, and what tblHVAC looks like after I run the query.

I hope the attachment works right...

 

[doomik]

how do I tell if my attachment came through??:banghead:

 

[plog]

and what tblHVAC looks like after I run the query

Nope. What you want tblHVAC to look like after you run the query.