INSERT INTO statement in Access

[Flo100]

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.

[JKing]

You've used square brackets wrongly in alot of places. Give this a try:
[code=sql]
INSERT INTO [QUESTOR Run tbl1] ([ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost])
SELECT [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'
[/code]

[Flo100]

You've used square brackets wrongly in alot of places. Give this a try:
[code=sql]
INSERT INTO [QUESTOR Run tbl1] ([ID], [QUE$TOR Version Name], [Project ID], [Cost Tab], [Group], [Item], [Quantity], [Unit Rate], [Cost])
SELECT [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'
[/code]

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].

[JKing]

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.

[ADezii]

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:[CODE=sql]INSERT INTO [QUE$TOR 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"));[/CODE]

[Flo100]

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'

[Flo100]

Preface the Fields in the SELECT Clause with the proper Table Identifier and you should be OK:[CODE=sql]INSERT INTO [QUE$TOR 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"));[/CODE]

I just tried like what you described above. it still gives me the same error.

[Flo100]

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.