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

INSERT INTO statement in Access

P: 31
Hello,

I have a table [QUESTOR Run tbl] and a duplicate table [QUESTOR Run tbl1] whose definition is smiliar to T but is empty without any values and also no primary key. my task is to

insert into QUESTOR Run tbl1 some values from QUESTOR Run tbl
I used the following Query:


INSERT INTO [QUESTOR Run tbl1][ID[, QUE$TOR Version Name[, Project ID[, Cost Tab[, Group[, Item[, Quantity[, Unit Rate[, Cost]]]]]]]]] SELECT [[QUESTOR Run tbl.]ID[, QUE$TOR Version Name[, Project ID[, Cost Tab[, Group[, Item[, Quantity[, Unit Rate[, Cost]]]]]]]] FROM [QUESTOR Run tbl] WHERE [QUESTOR Run tbl].[QUE$TOR Version Name]='7.3'

It comes up with an error in INSERT INTO statement.

What am i doing wrong here. I need help. Thank you.
Jul 23 '07 #1
Share this Question
Share on Google+
7 Replies


JKing
Expert 100+
P: 1,206
You've used square brackets wrongly in alot of places. Give this a try:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [QUESTOR Run tbl1] ([ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost])
  2. SELECT [ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost]
  3. FROM [QUESTOR Run tbl] 
  4. WHERE [QUESTOR Run tbl].[QUE$TOR Version Name]='7.3'
  5.  
Jul 23 '07 #2

P: 31
You've used square brackets wrongly in alot of places. Give this a try:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [QUESTOR Run tbl1] ([ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost])
  2. SELECT [ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost]
  3. FROM [QUESTOR Run tbl] 
  4. WHERE [QUESTOR Run tbl].[QUE$TOR Version Name]='7.3'
  5.  

Thank you very much. I used the same and now it gives me a Run time Error 3079: The specified field '[Project ID]' could refer to more than one table listed
in the FROM clause of your SQL statement. Where is the mistake? I am talking data from one table [QUESTOR Run Tbl] into [ QUESTOR Run tbl1].
Jul 23 '07 #3

JKing
Expert 100+
P: 1,206
Did you alter what I wrote at all?
I don't believe you need to specify tablenames for all fields in this case.

Make sure your FROM line only has the one table name.
Jul 23 '07 #4

ADezii
Expert 5K+
P: 8,597
Thank you very much. I used the same and now it gives me a Run time Error 3079: The specified field '[Project ID]' could refer to more than one table listed
in the FROM clause of your SQL statement. Where is the mistake? I am talking data from one table [QUESTOR Run Tbl] into [ QUESTOR Run tbl1].
Preface the Fields in the SELECT Clause with the proper Table Identifier and you should be OK:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [QUE$TOR Run tbl1] ( ID, [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], Item, Quantity, [Unit Rate], Cost )
  2. SELECT [QUESTOR Run tbl].ID, [QUESTOR Run tbl].[QUE$TOR Version Name], [QUESTOR Run tbl].[Project ID], 
  3. [QUESTOR Run tbl].[Cost Tab], [QUESTOR Run tbl].Group, [QUESTOR Run tbl].Item, [QUESTOR Run tbl].Quantity, 
  4. [QUESTOR Run tbl].[Unit Rate], [QUESTOR Run tbl].Cost
  5. FROM [QUESTOR Run tbl]
  6. WHERE ((([QUESTOR Run tbl].[QUE$TOR Version Name])="7.3"));
Jul 23 '07 #5

P: 31
Did you alter what I wrote at all?
I don't believe you need to specify tablenames for all fields in this case.

Make sure your FROM line only has the one table name.

Yes I did, Infact I tried icluding the full name for each field like :

INSERT INTO [QUESTOR Run tbl1] ([ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [GROUP], [Item], [Quantity], [Unit Rate], [Cost])
SELECT [QUESTOR Run tbl].[ID], [QUESTOR Run tbl].[QUE$TOR Version Name], [QUESTOR Run tbl].[Project ID], [QUESTOR Run tbl].[Cost Tab], [QUESTOR Run tbl].[GROUP], [QUESTOR Run tbl].[Item], [QUESTOR Run tbl].[Quantity], [QUESTOR Run tbl].[Unit Rate], [QUESTOR Run tbl].[Cost]
FROM [QUESTOR Run tbl]
WHERE [QUESTOR Run tbl].[QUE$TOR Version Name]='7.3'
Jul 23 '07 #6

P: 31
Preface the Fields in the SELECT Clause with the proper Table Identifier and you should be OK:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [QUE$TOR Run tbl1] ( ID, [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], Item, Quantity, [Unit Rate], Cost )
  2. SELECT [QUESTOR Run tbl].ID, [QUESTOR Run tbl].[QUE$TOR Version Name], [QUESTOR Run tbl].[Project ID], 
  3. [QUESTOR Run tbl].[Cost Tab], [QUESTOR Run tbl].Group, [QUESTOR Run tbl].Item, [QUESTOR Run tbl].Quantity, 
  4. [QUESTOR Run tbl].[Unit Rate], [QUESTOR Run tbl].Cost
  5. FROM [QUESTOR Run tbl]
  6. WHERE ((([QUESTOR Run tbl].[QUE$TOR Version Name])="7.3"));

I just tried like what you described above. it still gives me the same error.
Jul 23 '07 #7

P: 31
I just tried like what you described above. it still gives me the same error.
I got it. My first mistake was in the [QUESTOR Run tbl] the field [Project ID] did not have a space meaning it was [ProjectID] and there was a single quote missing after 7.3

My mistakes were silly. sorry about that. And thank you very much for helping.
Jul 23 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.