Solved Fill Access Table by using INSERT INTO ..SELECT..

Gizem

Member
Local time
Today, 22:35
Joined
Nov 29, 2021
Messages
30
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:
IDPositionP_NAME

Table2:
P_IDP_NAME_L
222P1Volkswagen_Golf
312P1Volkswagen_Polo
999P2Volkswagen_Passat
221P2Volkswagen_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 :)
 
Code:
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..
 
Code:
Dim SQLInsert As String
SQLInsert = " INSERT INTO Table1(P_NAME)  " _
    & "SELECT Table2.P_NAME_L " _
    & " FROM Table2" _
    & " where p_id<>"999P2"
CurrentDb.Execute SQLInsert
 
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
 
Why would you be basing an insert query from a report?
What is F_Projekt - a form?
 
Code:
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
 
Is GEWICHT_KG text or number, if number you don't need to wrap the 0 in single quotes.
 
I tried it for my Report but it does not work. I get a syntax error : syntax error in FROM clausel..
 
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
 
check the names of the table and fields
or lay out an example of the database
1643702109756.png

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

Attachments

  • 1643702072542.png
    1643702072542.png
    86 KB · Views: 297
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?
 
this is more like a report from the entire table, and you need to set a selection when opening the report
 
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.
 
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
 
lay out a sample of the database to make a form on it (and possibly a report)
 

Users who are viewing this thread

Back
Top Bottom