?
Solved

Insert statement using Select *

Posted on 2009-04-20
7
Medium Priority
?
502 Views
Last Modified: 2013-11-27
Hi,

Not sure why this is getting me stuck but it is.

I have a pivot query in access that obvisouly creates columns based on the pivot, hence the column names can change. So I was trying to insert the contents of the pivot query into a table like so.

INSERT INTO tbl_output ( Column1, column2, column3, column4, column5)
SELECT * FROM qry_pivot

However I keep getting the error

"The INSERT INTO statement contains the following unknown field name: 'sAge_item'. Make sure you have typed the name correctly, and try the operation again.
0
Comment
Question by:gardmanIT
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24185411
as the error message indicates, you specified a column somewhere that is not correct.
please triple-check the column names...
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24185497
When you list the columns to be inserted into, it is also best if you list the columns in the select statement.  This way, if someone adds a new column to the qry_pivot table, the insert .. select statement will still work.  Try listing the columns, this may also solve your problem:

INSERT INTO tbl_output ( Column1, column2, column3, column4, column5)
SELECT c1, c2, c3, c4, c5 FROM qry_pivot
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 24185732
As a3 said, the field names are mismatched.

< hence the column names can change.>
My understanding is that you are unable to predict the column names in the source query.

Since you are using a pivot table, a VBA solution might be needed to handle the changing column names by aliasing them to the fieldnames in the destination table:


Dim rs as DAO.recordset
Dim strSQL as string
Set rs = currentDB.openrecordset ("qryPivot", dbopendynaset)
 
strSQL = "INSERT INTO tbl_output ( Column1, column2, column3, column4, column5) "
strSQL = strSQL & " SELECT " & rs.fields(0).Name & " AS column1, "  &  rs.fields(1).Name &  " AS column2, "  &  rs.fields(2).Name & " as Column3, "  &  rs.fields(3).Name & " AS column4, "  &  rs.fields(4).Name & "  As Column5 FROM qryPivot "
 
Currentdb.Execute strSQL, dbfailonerror
rs.close
set rs = nothing

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:gardmanIT
ID: 24186626
@angelll

The column name the error is refering to is in the source pivot query which is referenced usign the "SELECT * FROM qry_pivot" Hence this cannot be the case.

@Milleniumaire

I cannot list the columns I am select as they are built from a pivot query.

So where say the data before the pivot is
sage_item | sage_value
a101           10
a102           12
a103           20

The column names would be

A101, a102, a103

As expected from a pivot query and hence why i was using select * from rather than explicitly naming the columns.

@mbizup

A solution that would seem to be the next viable step.. however I cannot understand why a simple insert into with a select * from is not working and why it would decide to error refering to a column name from the source table when im not even specifying the source tables columns.

Is there something I dont know about that prevents you using select * from when inserting.. why would be beyond me??

Source pivot table has the following structure
sAge_Item
sAge_Period_1 <-whatever the retured value is
sAge_Period_2 <-whatever the retured value is
sAge_Period_3 <-whatever the retured value is

*as said before its a pivot query hence the column names will actually be named as the periods that appear in the table that the query is built from.

Target table has the following structure
Item_code
Period_1
Period_2
Period_3

0
 
LVL 61

Expert Comment

by:mbizup
ID: 24186778
<I dont know about that prevents you using select * from when inserting>

Using a wildcard to select 'all' field names, you need exact fieldname matches between the source and destination.

If you spell out the exact fieldnames from the source table or query, you do not need exact matches, just the correct number of columns.  In fact the aliases in my own code may be overkill.  The overall purpose of the code is to allow you to select unknown fieldnames by number.

I couldn't tell you why, but that's the way it is.
0
 
LVL 4

Author Comment

by:gardmanIT
ID: 24311953
Dynamically building the fields and the columns to insert into was the way to go with this.

Not only did I have the problem of the column names being unpredictable I also ran into the problem of the number of columns being unpredicatable.

So what I did was create a table that I would populate with the three different pivot tables that produced the data I was interested in. I set a hard limit of columns to be produce by each of the 3 pivot queries then used code to either bulk out the insert statement with null columns or clip the extra columns found... in my case this was fine as I was never after more than 18 months of data so if i got 19 I could ignroe the 19th column. if I got 17 columns I would get the 18th column with nothing in it which was the best solution.

Thanks to all for the suggestions, I feel mbizup hit the nail on the head as far as having a solution that did not direclty fix my original question but was the next logical solution. I then expanded on that by looping thorugh to get the fields as said above.

THanks

Antony
Function insert_data()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim xField, xCountC, x, xConcat, SetColumnsC, SetColumnsF, SetColumnsV, xCountF, xCountV
    
    
    'clear out the destination table
    CurrentDb.Execute ("Delete from tbl_output_Ageing")
    
    'Build the field destination list
    xField = "Item_code , Description, QTY, Volume, [Product Group], [Value], Store, [Intro Date], [Parent Code], UOM, [Oldest Stock], [Due Out], " & _
                "[Current Period QTY] , [Current Period QTY - 1], [Current Period QTY - 2], [Current Period QTY - 3], [Current Period QTY - 4], " & _
                "[Current Period QTY - 5], [Current Period QTY - 6], [Current Period QTY - 7], [Current Period QTY - 8], [Current Period QTY - 9], " & _
                "[Current Period QTY - 10], [Current Period QTY - 11], [Current Period QTY - 12], [Current Period QTY - 13], [Current Period QTY - 14], " & _
                "[Current Period QTY - 15], [Current Period QTY - 16], [Current Period QTY - 17], [Current Period QTY - 18], [10701], [0], [Item Code_1], " & _
                "STORE_1, [Forecast Period], [Forecast Period 1], [Forecast Period 2], [Forecast Period 3], [Forecast Period 4], [Forecast Period 5], " & _
                "[Forecast Period 6], [Forecast Period 7], [Forecast Period 8], [Forecast Period 9], [Forecast Period 10], [Forecast Period 11], qry_pivot_the_value_sAge_Item, " & _
                "qry_pivot_the_value_DITMD, qry_pivot_the_value_QTY, qry_pivot_the_value_MUDN3, qry_pivot_the_value_GIGRP1, qry_pivot_the_value_£SDSV, qry_pivot_the_value_ESTOR3, " & _
                "qry_pivot_the_value_MUDA2, qry_pivot_the_value_MUDA3, qry_pivot_the_value_UOMTU, qry_pivot_the_value_AgePeriod, [qry_pivot_the_value_Due Out], [Oldest Period Value], " & _
                "[Current Period Value], [Current Period Value - 1], [Current Period Value - 2], [Current Period Value - 3], [Current Period Value - 4], [Current Period Value - 5], " & _
                "[Current Period Value - 6], [Current Period Value - 7], [Current Period Value - 8], [Current Period Value - 9], [Current Period Value - 10], [Current Period Value - 11], " & _
                "[Current Period Value - 12], [Current Period Value - 13], [Current Period Value - 14], [Current Period Value - 15], [Current Period Value - 16], [Current Period Value - 17], [Current Period Value - 18], V10701, V0 "
    
    
    
    'Set the amount of columns that we are working on for the Current
    SetColumnsC = 33
    'Set the amount of columns that we are working on for the Current
    SetColumnsF = 14
    'Set the amount of columns that we are working on for the Current
    SetColumnsV = 34
    
    
    
    'Get column count from the seperate tables(used only for working out columns) -2 for the two crap fields at the end
    xCountC = FieldExists2("qry_pivot_the_shizzle") - 2
    
    'Get column count from the seperate tables(used only for working out columns)
    xCountF = FieldExists2("qry_Pivot_Forecast") - 1
    
    'Get column count from the seperate tables(used only for working out columns) -2 for the two crap fields at the end
    xCountV = FieldExists2("qry_pivot_the_value") - 2
    
    'build the first part of the insert statement with the fields generated from above
    strSQL = "INSERT INTO tbl_output_Ageing ( " & xField & ") "
    
    '=============================================
    'Start the process for each of the 3 queries
    '=============================================
    '1-Query for Current
    '==========================================================
    
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_pivot_the_shizzle", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountC
        xConcat = xConcat & "[qry_pivot_the_shizzle." & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsC > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    
    '2-Query for Forecast
    '==========================================================
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_Pivot_Forecast", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountF + 1
        'MsgBox rs.Fields(x).Name & " x=" & x
        xConcat = xConcat & "[" & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsF > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    '----------------------------------------------------------
    
    '3-Query for Value
    '==========================================================
    'Query the individual query to get the columns
    Set rs = CurrentDb.OpenRecordset("qry_pivot_the_value", dbOpenDynaset)
             
    'Set our starting point to 0
    x = 0
    
    Do While x < xCountV
        xConcat = xConcat & "[qry_pivot_the_value." & rs.Fields(x).Name & "]"
        xConcat = xConcat & ","
        x = x + 1
    Loop
    
    Do While SetColumnsV > x
        xConcat = xConcat & "[0],"
        x = x + 1
    Loop
    rs.Close
    '----------------------------------------------------------
    
    'Trim the "," of the end
    xConcat = Left(xConcat, Len(xConcat) - 1)
    
    strSQL = strSQL & " SELECT " & xConcat & " FROM qry_pivot_combined "
    'Debug.Print xConcat
    Debug.Print strSQL
    'Debug.Print xField
    'Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    Set rs = Nothing
End Function

Open in new window

0
 
LVL 4

Author Closing Comment

by:gardmanIT
ID: 31578348
Please see above final post for more info on my acceptance of the solution.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 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