Solved

How to insert records from a query into a table

Posted on 2015-01-08
22
123 Views
Last Modified: 2015-01-12
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 = ""

Open in new window

0
Comment
Question by:Jass Saini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 3
22 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40538914
Don't know what problem you are having, but your syntax is wrong.  The syntax for an append generally looks like:

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 = "SomeValue"

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.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40538932
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40538945
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.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Jass Saini
ID: 40544593
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.


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 = ""
        

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544671
What do you get when you run this:

Select OrgName, CostCenter, Fund, PEC
from Final_Table_AllotQ
WHERE FINAL_TABLE_CROSSTAB.FINALID = ""

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.FINALID = ""
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40544690
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544700
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].OrgName
, [Final_Table_AllotQ].CostCenter
, [Final_Table_AllotQ].Fund
, [Final_Table_AllotQ].PEC
from Final_Table_AllotQ
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544719
Good catch, Pat.  I totally missed that!
0
 

Author Comment

by:Jass Saini
ID: 40544767
hello,

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

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544787
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

Open in new window

0
 

Author Comment

by:Jass Saini
ID: 40544799
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.
0
 

Author Comment

by:Jass Saini
ID: 40544807
Final_Table_AllotQ is a query ..does that make difference?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544913
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40544917
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
0
 

Author Comment

by:Jass Saini
ID: 40544952
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?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40545055
Are you still getting an error after changing the code to look like my last post
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 500 total points
ID: 40545062
"You cannot just enter a SQL statement in a form or control event and expect it to run. ---And why not Please explain."

You can paste a SQL string into a query and run it, and it will work just fine.  But if you want to run that query as a result of a particular form or control event (in this case the "UpdateTable_Exit" event), then you must use VBA or a Macro to run the query.  You could use a saved query or write the SQL, as I did in the post above, in the code module, but regardless, it must use proper VBA (Visual Basic for Applications) syntax.
0
 

Author Comment

by:Jass Saini
ID: 40545427
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!!!
0
 

Author Closing Comment

by:Jass Saini
ID: 40545432
Thank You!!!
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40545625
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.
0
 

Author Comment

by:Jass Saini
ID: 40545741
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40545789
Welcome to the life of a developer!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question