Sql Syntax Error

[heusden99]

I am trying to move data from one table to another in the same database.The fields in table tbliR are IDIR (numeric) and Desc (Memo)The fields in destination table tbl ER are IDIR (numeric) and Notes (Memo)SQL = "insert into tblER (IDIR, Desc) select (IDIR,Notes) from tblIR where Notes>' '"The fieldname desc seems to be the problem being a reserved name (DESC = Descending in sorting).When I change desc in desc2 in both query and table, then everything works fine.Sofar I haven't found anything on fieldname protocol advising not to use 'desc'or others as fieldname.Someone suggested to put Desc between [ ] , this stopped the error warning, but no records where added in the destination table.Who got the solution if any?Kind regards,Ton van Heusden

[deboni]

instead of the column name many dbms, if not all (except MySQL), accept the numric order of it in the select statementso things like select 1,2,3... are equivalent to select firstcolumnname, secondcolumnname, thirdcolumnname ....the sense of it is to facilitate writing generalized code depending on a certain structure rather than nouns, imagine system supposed to be mounted in different countries. That's just to say that using key words as names for columns remain an administrative mistake and a threat to system whether you bypass or not.regards

[heusden99]

Thanks for helping to solve my problem.Could you explain how the SQL would look like. I am using a MSACCESS database.regards,Ton

instead of the column name many dbms, if not all (except MySQL), accept the numric order of it in the select statementso things like select 1,2,3... are equivalent to select firstcolumnname, secondcolumnname, thirdcolumnname ....the sense of it is to facilitate writing generalized code depending on a certain structure rather than nouns, imagine system supposed to be mounted in different countries. That's just to say that using key words as names for columns remain an administrative mistake and a threat to system whether you bypass or not.regards

[deboni]

Thanks for helping to solve my problem.Could you explain how the SQL would look like. I am using a MSACCESS database.regards,Ton

Sorry I'm in Office now and i've just tried that way i suggested you and actually works differently than how i was expecting. Actually with oracle is possible to achive to the column orde but not in the select part, only in the order section. is possible to write

select * from table order by 1,2,3

. So I gave you wrong information.SO

insert into tblER (IDIR, Desc) select (IDIR,Notes) from tblIR where

could be

insert into tblER select (IDIR,Notes) from tblIR where...

must work but the select part of the query must fit the structure of the output table "tblER" if notyou have to add empty fields to fill up the record.suppose that the structure of your output table was:field1 textIDIR textNotes textfield2 textthen the insert query would be

insert into tblER select ('',IDIR,Notes,'') from tblIR where...

BUT IN ACCESS IS MUCH DIFFERENT

INSERT INTO tblER ( IDIR, [desc] )SELECT IDIR,NotesFROM tblIR;

regards