Jass Saini
asked on
How to insert records from a query into a table
I am having problems trying to insert records into my table from my crosstab query
[/Private Sub UpdateTable_Exit(Cancel As Integer)
"INSERT INTO(FINAL_TABLE_CROSSTAB)"
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINALID = ""
What "problem"? We don't have divine insight. Are you getting an error message? What is it? Are no records being inserted? Are the wrong records being inserted?
Dale has offered some good guesses. If your problem is different, please tells us what it is.
Dale has offered some good guesses. If your problem is different, please tells us what it is.
It could very well be your WHERE clause, since it is unlikely that the FinalID value of any of the records will be an empty string. It might be NULL, or have some other value but values of "" are rare in queries.
ASKER
Hello,
Sorry I was on vacation...The insert line and Select line are red...with the error being syntax error and highlighting the Table and query name. If that helps.
Sorry I was on vacation...The insert line and Select line are red...with the error being syntax error and highlighting the Table and query name. If that helps.
INSERT INTO [FINAL_TABLE_CROSSTAB](Org Name, CostCen, Fund, PEC)
Select [Final_Table_AllotQ] (OrgName, CostCenter, Fund, PEC)
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINALID = ""
What do you get when you run this:
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINAL ID = ""
You have a syntax problem with the INSERT portion of the code as well. All field names that have a space in them must be wrapped in [ ]'s, which is why most developers will use camel case (as you did with OrgName and CostCenter), or will place an underscore between words. Try:
INSERT INTO [FINAL_TABLE_CROSSTAB] ([Org Name], CostCen, Fund, PEC)
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINAL ID = ""
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINAL
You have a syntax problem with the INSERT portion of the code as well. All field names that have a space in them must be wrapped in [ ]'s, which is why most developers will use camel case (as you did with OrgName and CostCenter), or will place an underscore between words. Try:
INSERT INTO [FINAL_TABLE_CROSSTAB] ([Org Name], CostCen, Fund, PEC)
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINAL
Also your where clause doesn't make sense. Why is the where checking the target table? You need to remove the where clause entirely unless there is some criteria you want to apply against Final_Table_AllotQ.
This is not a valid syntax for a select statement:
Select [Final_Table_AllotQ] (OrgName, CostCenter, Fund, PEC)
from Final_Table_AllotQ
You can use:
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
or
Select [Final_Table_AllotQ].OrgNa me
, [Final_Table_AllotQ].CostC enter
, [Final_Table_AllotQ].Fund
, [Final_Table_AllotQ].PEC
from Final_Table_AllotQ
Select [Final_Table_AllotQ] (OrgName, CostCenter, Fund, PEC)
from Final_Table_AllotQ
You can use:
Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
or
Select [Final_Table_AllotQ].OrgNa
, [Final_Table_AllotQ].CostC
, [Final_Table_AllotQ].Fund
, [Final_Table_AllotQ].PEC
from Final_Table_AllotQ
Good catch, Pat. I totally missed that!
ASKER
hello,
Sorry..I am not good at coding.
Now I am getting a Compile error Expected: Case On the Select line.
Sorry..I am not good at coding.
Now I am getting a Compile error Expected: Case On the Select line.
Private Sub UpdateTable_Exit(Cancel As Integer)
INSERT INTO([FINAL_TABLE_CROSSTAB])(OrgName, CostCenter, Fund, PEC)
Select [Final_Table_AllotQ].OrgName, [Final_Table_AllotQ].CostCenter, [Final_Table_AllotQ].Fund, [Final_Table_AllotQ].PEC)
from Final_Table_AllotQ
You cannot wrap the destination table name in ( ) and you have an extra ) at the end of the SELECT line. You also cannot put this into a VBA event without assigning it to an action.
Private Sub UpdateTable_Exit(Cancel as Integer)
Dim strSQL as string
strSQL = "INSERT INTO [FINAL_TABLE_CROSSTAB] (OrgName, CostCenter, Fund, PEC) " _
& "Select OrgName, CostCenter, Fund, PEC " _
& "from Final_Table_AllotQ"
Currentdb.Execute strSQL, dbfailonerror
End Sub
ASKER
Hello,
Got rid of the extra ) at the end of the SELECT line...Same error
Compile error:
Expected: Case
and it highlights the Final_Table_AllotQ..if that helps.
Got rid of the extra ) at the end of the SELECT line...Same error
Compile error:
Expected: Case
and it highlights the Final_Table_AllotQ..if that helps.
ASKER
Final_Table_AllotQ is a query ..does that make difference?
Did you add the rest of the code I just posted?
You cannot just enter a SQL statement in a form or control event and expect it to run.
You cannot just enter a SQL statement in a form or control event and expect it to run.
What happens if you create a new query, paste the following SQL statement into the query and run it?
Select OrgName, CostCenter, Fund, PEC from Final_Table_AllotQ
Select OrgName, CostCenter, Fund, PEC from Final_Table_AllotQ
ASKER
The query works...but I am trying to get the to paste into my table...
Did you add the rest of the code I just posted?---yes I did
You cannot just enter a SQL statement in a form or control event and expect it to run. ---And why not Please explain. I thought the whole thing was functional...Then how would I get my event to run?
Did you add the rest of the code I just posted?---yes I did
You cannot just enter a SQL statement in a form or control event and expect it to run. ---And why not Please explain. I thought the whole thing was functional...Then how would I get my event to run?
Are you still getting an error after changing the code to look like my last post
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help...just had a meeting with the boss's boss and he change everything again. I am so tired of this DB now!!!
ASKER
Thank You!!!
JAss SAini,
You will have a lot less trouble if you simply build the queries using the QBE. Once the query is syntactically correct, you can copy the sql string into VBA if you want to although there is no reason to do that when you can just run the querydef and save the hassle of trying to get the syntax right.
You will have a lot less trouble if you simply build the queries using the QBE. Once the query is syntactically correct, you can copy the sql string into VBA if you want to although there is no reason to do that when you can just run the querydef and save the hassle of trying to get the syntax right.
ASKER
Thanks Pat...that's what I did..But If I change the table..i need to change the query and the form. I have been working on this DB for eight months now..everytime I think I am done..they change the table again.
Welcome to the life of a developer!
INSERT INTO [tablename] (field1, field2, field3)
SELECT Field1, Field2, Field3
FROM sometableOrQuery
WHERE SomeTableOrQuery.FinalID = "someValue"
You can do this without listing the fields (row #1 above) if the fields in the destination table and in the query are exact matches in number, sequence, and datatype. The syntax for that would look like:
INSERT INTO [TableName]
SELECT [SomeTableOrQuery].*
FROM [SomeTableOrQuery]
WHERE [SomeTableOrQuery].FinalID
But because this is a crosstab query, it is unlikely that you will always have the same number of columns in the crosstab. for that reason, I would strongly recommend that you consider:
1. defining the column headers in your crosstab query, so that they will always be the same and come up in the same order, even if there is no data in the column.
2. use the syntax where you actually list the field names you are appending to.