what's wrong with this sql statement?

Question

in access i am doing this:

insert into accounts_changes 
 (select * 
  from accounts 
  where [Agency Code] 
  in (select * from tableimport))

it says that it doesnt like this INSERT statement

update:

sSql = "insert into accounts_changes (select * from Accounts where [Agency Code] in (select [Agency Code] from tableimport))"

i did what mark said and it is still giving me the same error message

syntax error in INSERT INTO statement

when i do this:

ssql = "select [Agency Code] from tableimport"
CurrentDb.Execute ssql

it says CANNOT EXECUTE SELECT QUERY

Answer 1

You probably need to select the agency code from tableimport instead of *.

Answer 2

This is wrong:

select *
from accounts
where [Agency Code] in (select * from tableimport)

You can only select one column in the subquery for an IN clause. You want something like this:

select *
from accounts
where [Agency Code] in (select [Agency Code] from tableimport)

You need to check the exact name of the column in the table tableimport. The above is just my best guess.

Answer 3

You should specify the column you'd like to search on tableimport inner select.

Also, you could declare the fields of the table being inserted and the fields returned:

INSERT INTO TABLE_EXAMPLE (A, B) SELECT AA, BB FROM TABLE_ORIGIN

Answer 4

this worked:

insert into accounts_changes select * from Accounts where [Agency Code] in (select [Agency Code] from tableimport)

Answer 5

This part may have misled you:

ssql = "select [AgencyCode] from tableimport"
CurrentDb.Execute ssql

Execute requires an "action" query (INSERT, DELETE, UPDATE, or SELECT INTO). When you give Execute a plain (row returning) SELECT query, you will always get error #3065, "Cannot execute a select query". It doesn't mean there was anything wrong with your SELECT statement. Test your SELECT statement by pasting it into SQL View of a new query.

You showed two variations of [AgencyCode] ... one with and another without a space between Agency and Code. Which is it?

I think your original INSERT statement had an extra pair of parentheses which aren't needed. Try it this way:

insert into accounts_changes
select * 
from Accounts
where [Agency Code] in (
    select [Agency Code] from tableimport)

If that still fails, verify you have the same number of fields, with the same field names and data types in both Accounts and accounts_changes. If the fields in the two tables don't match exactly, list the fields explicitly as @pcent showed you.