Appending to a table I am querying

[Peter Bellamy]

I am sure this is straight forward but I just can't seem to crack it!

I have a table which is linked Excel spreadsheet and another which is a copy of some of it which I use with my Db forms and reports.
I am trying to append any missing records from the SS table into the other.

The query I have designed works perfectly as a Select Q' but not as an Append. It says one field is duplicated.
The duplicated field is the one I am using in a "Is Null' test to find the 'missing' records.
When I look at the SQL sure enough that field is duplicated in the INSERT line.

The only solution I can see is to use a Make Table query for the missing records and then append that to my destination query.
But seems so clumsy !

Any suggestions?

Cheers
Peter

 

[neileg]

Please post the SQL

 

[Peter Bellamy]

The second [Text] is the problem. It is from the destination table and as you can see in the last line, is used as the Is Null test for missing records.

INSERT INTO [Copy Gas Leakage Tests] ( [Text], Result, Measure, Unit, Pressure, Unit1, [Date], [Time], [Text] )
SELECT CWHGasTests18.Text, CWHGasTests18.Result, CWHGasTests18.Measure, CWHGasTests18.Unit, CWHGasTests18.Pressure, CWHGasTests18.Unit1, CWHGasTests18.Date, CWHGasTests18.Time, [Gas Leakage Tests].Text
FROM CWHGasTests18 LEFT JOIN [Gas Leakage Tests] ON CWHGasTests18.Text = [Gas Leakage Tests].Text
WHERE (((CWHGasTests18.Result)="(OK)") AND (([Gas Leakage Tests].Text) Is Null));

Cheers
Peter

 

[neileg]

I think you will find that the duplication is here:

INSERT INTO [Copy Gas Leakage Tests] ( [COLOR="Red"][Text][/COLOR], Result, Measure, Unit, Pressure, Unit1, [Date], [Time], [COLOR="red"][Text] [/COLOR])

By the way, Text is a reserved word and should not be used as an object name.

 

[Peter Bellamy]

The 'text' comes from an external program so I cannot change, but have changed the copy table and this has solved it ! ?

Thanks