Solved Fill Access Table by using INSERT INTO ..SELECT.. (1 Viewer)

[Gizem]

Hallo together,

few months ago you helped me a lot. Now i need help again. Now i want to fill my Acces Table with an other Acces Table. I tried it with INSERT INTO.. SELECT.. , but it does not work. Maybe you have a better solution or you can find my failure. I chose this option becauce, i want to transport not all rows and columns.

Table1:

ID	Position	P_NAME

Table2:

P_ID	P_NAME_L
222P1	Volkswagen_Golf
312P1	Volkswagen_Polo
999P2	Volkswagen_Passat
221P2	Volkswagen_Tiguan

My goal is to fill Table1, column P_NAME with Table2 column P_NAME_L. But i have to check in table2 P_ID. If P_ID starts with 9 (the red one) than i have to ignore the hole row and got to the next.

I try to solve this problem since two days. Thank you in advance

 

[Gizem]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Table1  " _
    & "SELECT Me!Table2.P_NAME_L " _
    & " FROM Table2;"
CurrentDb.Execute SQLInsert

I get a run-tim-error 3061.To few parameters. Expected 1..

 

[SHANEMAC51]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Table1(P_NAME)  " _
    & "SELECT Table2.P_NAME_L " _
    & " FROM Table2" _
    & " where p_id<>"999P2"
CurrentDb.Execute SQLInsert

 

[Gizem]

    & " where p_id<>"999P2"

[/QUOTE]
Is this part correct?

 

[SHANEMAC51]

ERROR----------
& " where p_id<>'999P2'"

 

[Gizem]

ERROR----------
& " where p_id<>'999P2'"

tank you

 

[Gizem]

Now i have to do the same with a Report. My source is now a Report. Here it does not work. Do you see where the failure is ?
Dim SQLInsert As String
SQLInsert = " INSERT INTO Table3(P_NAME) " _
& "SELECT F_Projekt.P_NAME " _
& " FROM F_Projekt" _
& " WHERE GEWICHT_KG <> '0' "

CurrentDb.Execute SQLInsert

I get an error 3131: Syntax Error in FROM clause

 

[Minty]

Why would you be basing an insert query from a report?
What is F_Projekt - a form?

 

[SHANEMAC51]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Table3(P_NAME) " _
& " SELECT F_Projekt.P_NAME"  _
& " FROM F_Projekt"  _
& " WHERE GEWICHT_KG <> '0' "

CurrentDb.Execute SQLInsert

 

[Gizem]

Why would you be basing an insert query from a report?
What is F_Projekt - a form?

No it is a report

 

[bastanu]

Is GEWICHT_KG text or number, if number you don't need to wrap the 0 in single quotes.

 

[Gizem]

I tried it for my Report but it does not work. I get a syntax error : syntax error in FROM clausel..

 

[SHANEMAC51]

I tried it for my Report but it does not work. I get a syntax error : syntax error in FROM clausel..

check the names of the table and fields
or lay out an example of the database

 

[Gizem]

check the names of the table and fields
or lay out an example of the database

It seems like this. But it isn't the same

 

[June7]

F_Projekt is a report? Why does it have prefix of F?

Cannot pull data from a report like that. Why do you not pull from table(s) since that's where the data is?

 

[SHANEMAC51]

this is more like a report from the entire table, and you need to set a selection when opening the report

 

[Gizem]

F_Projekt is a report? Why does it have prefix of F?

Cannot pull data from a report like that. Why do you not pull from table(s) since that's where the data is?

Yes it is a report. I don't know
I have to pull data from this report. My superior ruled it.

 

[Gizem]

this is more like a report from the entire table, and you need to set a selection when opening the report

Can you give me an example

 

[SHANEMAC51]

you need to create a form with search fields by last name, first name, position .....
in it the boss will see what is needed

the report is intended for another

 

[SHANEMAC51]

lay out a sample of the database to make a form on it (and possibly a report)