uppend query fails to insert unmatched records

[MilaK]

I would like to insert records to "tbl_Variants" where [ID] (primary key) doesn't match records from the "tbl_Variant_qry" table. There is one record that fits this description however the query fails to insert that records. Please suggest how to fix the query. Thanks, Mila

Here is the sql:

INSERT INTO tbl_Variants ( sample_id, gene, exon, cDNA, aa_change, ID )
SELECT tbl_Variants.sample_id, tbl_Variants.gene, tbl_Variants.exon, tbl_Variants.cDNA, tbl_Variants.aa_change, tbl_Variants.ID
FROM tbl_Variant_qry INNER JOIN tbl_Variants ON tbl_Variant_qry.[ID] = tbl_Variants.[ID]
WHERE (([tbl_Variants].[ID]<>([tbl_Variant_qry].[ID])));

 

[sneuberg]

Unmatched queries like you are trying to create have outer joins and Is Null in the criteria so your query doesn't look right. I suggest you try the query wizard which will create an unmatched query for you. Most of the time it gets it right. You should be able to convert whatever it comes up with to an append query.

If that fails you can read up on how to create these yourself in this WEB page.

 

[kevlray]

Maybe I missed something, but you are doing a inner join on tbl_Variant_qry and tbl_Variants which will only return the records that match, then you put in a where clause when they do not match?!?

 

[MilaK]

I've tried the following unmatched record query that returns one record that is present in the "tbl_Variant_qry" table but not in the "tbl_Variant" table, however, when I convert the select query to an Append query shown below I get an error message "You've tried to assign value to a variable that is not Variant data type". I'm not sure what's wrong?

INSERT INTO tbl_Variants ( sample_id, flag, gene, exon, cDNA, aa_change, [%Alt], FR, RR, FA, cosmic_id, transcript, [position], 1000G_freq, dbSNP, Review_One, Review_Two, revised_aa_change_One, revised_cdna_One, revised_aa_change_Two, revised_cdna_Two, RA, ID )

SELECT tbl_Variant_qry.sample_id, tbl_Variant_qry.flag, tbl_Variant_qry.gene, tbl_Variant_qry.exon, tbl_Variant_qry.cDNA, tbl_Variant_qry.aa_change, tbl_Variant_qry.[%Alt], tbl_Variant_qry.FR, tbl_Variant_qry.RR, tbl_Variant_qry.FA, tbl_Variant_qry.cosmic_id, tbl_Variant_qry.transcript, tbl_Variant_qry.position, tbl_Variant_qry.[1000G_freq], tbl_Variant_qry.dbSNP, tbl_Variant_qry.Review_One, tbl_Variant_qry.Review_Two, tbl_Variant_qry.revised_aa_change_One, tbl_Variant_qry.revised_cdna_One, tbl_Variant_qry.revised_aa_change_Two, tbl_Variant_qry.revised_cdna_Two, tbl_Variant_qry.RA, tbl_Variants.ID

FROM tbl_Variant_qry LEFT JOIN tbl_Variants ON tbl_Variant_qry.[ID] = tbl_Variants.[ID]

WHERE (((tbl_Variants.ID) Is Null));

 

[sneuberg]

The only thing I can tell you to do is the check each field in the INSERT INTO list with the SELECT list to make sure the types match.

If you can upload the database or a copy with this part I'll see if I can figure out what's wrong.