By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

SQL synthax problem on INSERT INTO instruction.

P: n/a
I am quite new with Access, so please be indulgent if my question
sound a little bit newbish.

I have two table in the same database: DOSSIER and MAIN.

MAIN is having already formatted data that I need to import in
DOSSIER, so everything is ready.

I used help to determine the correct synthax to do this with SQL but
it keeps saying that my synthax is wrong, here are my lines:

INSERT INTO DOSSIER [([DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])]
SELECT [MAIN].[noDossier], [MAIN].[sup_visee], [MAIN].[dec_cptaq],
[MAIN].[sup_auto_cptaq], [MAIN].[rev_cptaq], [MAIN].[sup_auto_rev]
FROM MAIN;

Can someone tell me what error I did? I tried a lot of different
synthax (some involving the command "IN" as the help state, but does
it apply to me, since the data I want to import are already in the
database?) and I am a bit discouraged now.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 10 Jun 2005 07:22:31 -0700, fr******@gmail.com (John Marble) wrote:

Syntax looks good to me, provided data types in both tables are the
same. Debug this by pasting the sql into a new query, switching to
design view, and running it. Access will point out where the error is.

-Tom.

I am quite new with Access, so please be indulgent if my question
sound a little bit newbish.

I have two table in the same database: DOSSIER and MAIN.

MAIN is having already formatted data that I need to import in
DOSSIER, so everything is ready.

I used help to determine the correct synthax to do this with SQL but
it keeps saying that my synthax is wrong, here are my lines:

INSERT INTO DOSSIER [([DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])]
SELECT [MAIN].[noDossier], [MAIN].[sup_visee], [MAIN].[dec_cptaq],
[MAIN].[sup_auto_cptaq], [MAIN].[rev_cptaq], [MAIN].[sup_auto_rev]
FROM MAIN;

Can someone tell me what error I did? I tried a lot of different
synthax (some involving the command "IN" as the help state, but does
it apply to me, since the data I want to import are already in the
database?) and I am a bit discouraged now.


Nov 13 '05 #2

P: n/a
Problem solved, it came from an EXCESS of synthax. It seems that trying
to copy the exact synthax that the help is giving you wasn't a good
solution.

For informative purpose, here is the corrected synthax:

INSERT INTO DOSSIER ( noDossier, sup_visee, dec_cptaq, sup_auto_cptaq,
rev_cptaq, sup_auto_rev )
SELECT MAIN.noDossier, MAIN.sup_visee, MAIN.dec_cptaq,
MAIN.sup_auto_cptaq, MAIN.rev_cptaq, MAIN.sup_auto_rev
FROM MAIN;

The problem was located in the first line, I had an extra "[]" that
screwed up everything, despite that it was in the help synthaxe.

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3

P: n/a
jr
try taking out [ ] as i have highlighted

INSERT INTO DOSSIER [([DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])]

so it looks like

INSERT INTO [DOSSIER], ([DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])

SQL Access is an arkward beast.
You Know you can do this much easier if you use an ACTION QUERY

Its simple

Create a new blank Query
Right click when mouse is in the quey design space
On the drop down left click SHOW TABLE
This will display a list of tables etc
Select MAIN table ( you can also select other queries etc - but no need on this occassion)
Then drag n drop the required fields from the table in the query design space
Save your query
Open it again in design mode
Right click when mouse is in the quey design space
On the drop down left click QUERY TYPE
If you intend to add data from main to dossier
Left click on APPEND QUERY
A display showing a drop down of table in your databese is now in view
Click on the table you want ie MAIN table
Then click OK

You have now created your action query?

This will add the selected field value rows
from [MAIN].[noDossier], [MAIN].[sup_visee],
[MAIN].[dec_cptaq], [MAIN].[sup_auto_cptaq],
[MAIN].[rev_cptaq], [MAIN].[sup_auto_rev])
INTO
[DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])
The only way you will get an error is if the fileds in MAIN have differing formats
from the fields in DOSSIER.
Also if the field names in MAIN differ from the field names in DOSSIER

Open the query in design mode
and you will see on row 1 your source fields and on row 2 your destination fields
select the appropriate destination fields from the row 2 field destination drop down .

john rutherford




"John Marble" <fr******@gmail.com> wrote in message news:28**************************@posting.google.c om...
I am quite new with Access, so please be indulgent if my question
sound a little bit newbish.

I have two table in the same database: DOSSIER and MAIN.

MAIN is having already formatted data that I need to import in
DOSSIER, so everything is ready.

I used help to determine the correct synthax to do this with SQL but
it keeps saying that my synthax is wrong, here are my lines:

INSERT INTO DOSSIER [([DOSSIER].[noDossier], [DOSSIER].[sup_visee],
[DOSSIER].[dec_cptaq], [DOSSIER].[sup_auto_cptaq],
[DOSSIER].[rev_cptaq], [DOSSIER].[sup_auto_rev])]
SELECT [MAIN].[noDossier], [MAIN].[sup_visee], [MAIN].[dec_cptaq],
[MAIN].[sup_auto_cptaq], [MAIN].[rev_cptaq], [MAIN].[sup_auto_rev]
FROM MAIN;

Can someone tell me what error I did? I tried a lot of different
synthax (some involving the command "IN" as the help state, but does
it apply to me, since the data I want to import are already in the
database?) and I am a bit discouraged now.


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.