using access sql to do INSERT INTO (aggregate function)

Question

here's my code:

insert into archive from temp where temp.field6>archive.field6

i would like to insert the entire row into a table archive from table temp where one field is greater than another.

what is wrong with my syntax? it is giving me ERROR ON INSERT InTO

edit:

here is what i have so far:

INSERT INTO archive
SELECT temp.*
FROM temp, archive
WHERE temp.field6>max(archive.field6);

im sorry i was completely wrong with the first query

please note the new MAX

i am getting an error because i cannot use the aggregate function here.

I am not even sure where to begin fixing that. You are missing a select (which needs to include the temp table and the archive table if you are going to have a comparison). Perhaps you can be more specific about what you are trying to accomplish and what you have already done to try and figure it out yourself. — lambacck, May 31 '11 at 02:14

score 3 · Accepted Answer · answered May 31 '11 at 02:33

3

INSERT INTO archive
SELECT temp.*
FROM temp
WHERE temp.field6>(SELECT max(archive.field6) FROM archive);

answered May 31 '11 at 02:33

lambacck

Grzegorz Szpetkowski · Answer 2 · 2011-05-31T02:34:35.277

1

According to MSDN you need to use more comprehensive syntax with SELECT. For example:

INSERT INTO archive SELECT temp.* FROM temp, archive 
GROUP BY temp.field6 HAVING temp.field6 > max(archive.field6);

edited May 31 '11 at 02:34

answered May 31 '11 at 02:05

thank you ive done exactly that but it is asking me for the values for temp.fiel6 and the other one – Alex Gordon May 31 '11 at 02:07
@I__: Yup, updated answer, but I am not sure is this what you want. I assume that archive and temp have same schema. Otherwise you need to specify column list for insert list and select list. – Grzegorz Szpetkowski May 31 '11 at 02:17
thank you again,. your query did not work. it did append rows but way too many. i tink there is an error. yes both tables have the same schema – Alex Gordon May 31 '11 at 02:22

2 Answers2