[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

need SQL to create new fields in a table from values found in an existing field

Posted on 2014-08-08
34
Medium Priority
?
233 Views
Last Modified: 2014-08-08
All - I need a query that creates additional fields in my table from unique/distinct values that are found in one of my existing fields.

Thanks.
0
Comment
Question by:Cam Raben
  • 17
  • 14
  • +2
34 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40248997
Your statement is ambiguous. Please provide more details. If you have existing data, providing a sample of the existing data and a mockup of what you want for the result will be helpful.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40249174
DML (Data Manipulation Language) queries (of which SELECT is one type) do NOT create columns in tables.  You need to create a DDL (Data Definition Language) query  that ALTER's the table structure.

PS - What you are envisioning is a spreadsheet NOT a relational database.  Your table is most likely not normalized and that is why you are asking how to do this.  The best course of action is to normalize the table so you won't need to add columns on the fly.  If you post your table description (with column definitions if they are not clearly named) we can help separate the columns in to one or more additional tables so that you are working with a normalized schema.  Once that's done, you won't ever worry about columns again:)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40249196
...adding to what Pat stated above...

... at some point you may run out of fields to add....
:-O
Access currently will only allow 255 fields... (in your scenario, ...what then?)

Besides, you cannot simply "create" a field, you must also define its datatype and set all of the associated properties...
   
    "Fields are expensive, rows are cheap"
;-)


JeffCoachman
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Cam Raben
ID: 40249206
@PatHartman - Thanks I know you're trying to help but, as I requested earlier I'd prefer to work with another expert as the assistance last time did not help me solve my problem...this is why I am trying again with this new post.  Many thanks.

@Shaun -

See attached (I just copied the first few rows from my table into an excel spreadsheet for simplicity).

The field titled [ELEMENT] contains 30 unique values, each of which need to be added as new fields in this table.  (decimal number format)

I'd also like to do the very same thing (add an extra set of the same 30 new fields) except that those would have the suffix -PQL.   (decimal number format)

They don't have to be populated with anything, just added to the structure of my table.  

Ex.  my table has this field:   ELEMENT.  Values in [ELEMENT] are Ag, Al, B, Ba, etc.

I need the table to have new fields:  [Ag]  [Ag-PQL]   [Al]   [Al-PQL]   etc for all 30.
table.xlsx
0
 

Author Comment

by:Cam Raben
ID: 40249214
Apparently a "transform" sql statement can do this....?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249518
Here is some code that will add the fields (to another table, though).  The element names are in tblElements; the newly created table is tblElementsPlus.  They are created as Double fields, because the dbDecimal type triggered an error:
Public Sub AddElementsToTable()

   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim strElement As String
   Dim tdf As DAO.TableDef
   Dim strSQL As String
   
   Set rst = CurrentDb.OpenRecordset("tblElements")
   Set tdf = CurrentDb.CreateTableDef("tblElementsPlus")
   
   Do While Not rst.EOF
      strElement = rst![Element]
      Debug.Print "Element: " & strElement
      tdf.Fields.Append tdf.CreateField(Name:=strElement, Type:=dbDouble)
      rst.MoveNext
   Loop
   
   tdf.Fields.Append tdf.CreateField(Name:="Element", Type:=dbText)
   CurrentDb.TableDefs.Append tdf
   
   Set rst = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   
   strSQL = "INSERT INTO tblElementsPlus ( Element )" _
      & "SELECT tblElements.Element FROM tblElements;"
   CurrentDb.Execute strSQL, dbFailOnError

End Sub

Open in new window

The sample database is attached.
Elements-Test.accdb
0
 

Author Comment

by:Cam Raben
ID: 40249556
I ran it inside my db and got the following run time error 94:  invalid use of null.  The problem is on the strElement =  rst....    line

Do While Not rst.EOF
      strElement = rst![Element]
      Debug.Print "Element: " & strElement
      tdf.Fields.Append tdf.CreateField(Name:=strElement, Type:=dbDouble)
      rst.MoveNext
   Loop

Could it be hitting a null value and crashing?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249566
Try deleting tblElementsPlus first.  I have run the code a few times and it always worked on my computer, so it might be some difference in environment.  What version of Access are you running?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249571
I could save the database in the .mdb format if you want to try that.
0
 

Author Comment

by:Cam Raben
ID: 40249603
Delete tblElementsPlus from where?   I am using 2010 version.  
My db is about 60 MB, so I can't easily send it, unless I strip it down some which I could do.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249617
You may need to close and reopen the database to see tblElementsPlus.  In Access 2010 there is a long-standing problem with newly created objects not being visible in the navigation pane.  I am also using Access 2010.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249620
I added these two lines after the Declarations section to delete tblElementsPlus if it exists:
On Error Resume Next
   DoCmd.DeleteObject acTable, "tblElementsPlus"   

Open in new window

Try adding these lines to the code in the database, and see if that works.
0
 

Author Comment

by:Cam Raben
ID: 40249628
The only place it exists is in the text of my macro you just sent.  I have attached the db since you are running the same thing.  It has only one table - the one I am working on.
TESTDWR-WQdata06to14---Copy.accdb
0
 

Author Comment

by:Cam Raben
ID: 40249638
I added the two lines at the bottom of the macro code, just above end sub, and it ran for a while and then said "not responding".
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249640
I tried to download the database, but just got a page of garbage characters.  Did you try the database I uploaded?  If you want to try the procedure with your table, you will need to modify the code for your table name and field name (the field that has the element name in it).  tblElementsPlus is created by the code.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249645
What is the name of your table, and the name of the field with the element name in it?  I can modify the procedure as needed so you can try it in your database.
0
 

Author Comment

by:Cam Raben
ID: 40249673
Yes, I changed your table name to match my own and the macro ran for a while and then got a "not responding" and nothing happened.  I'll try to upload mine one more time.   See attached.  I'd like the new fields to be in my existing table.
TESTDWR-WQdata06to14---Copy.accdb
0
 

Author Comment

by:Cam Raben
ID: 40249692
In case it doesn't download properly for you, here are the complete contents of my macro:

Public Sub AddElementsToTable()

   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim strElement As String
   Dim tdf As DAO.TableDef
   Dim strSQL As String
   
   Set rst = CurrentDb.OpenRecordset("06to14waters to match my table nametot")
   Set tdf = CurrentDb.CreateTableDef("tblElementsPlus")
   
   Do While Not rst.EOF
      strElement = rst![Element]
      Debug.Print "Element: " & strElement
      tdf.Fields.Append tdf.CreateField(Name:=strElement, Type:=dbDouble)
      rst.MoveNext
   Loop
   
   tdf.Fields.Append tdf.CreateField(Name:="Element", Type:=dbText)
   CurrentDb.TableDefs.Append tdf
   
   Set rst = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   
   strSQL = "INSERT INTO tblElementsPlus ( Element )" _
      & "SELECT 06to14watertot.Element FROM 06to14watertot"
   CurrentDb.Execute strSQL, dbFailOnError

End Sub
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249712
I have the same problem -- just get a page of garbage characters.  I noticed something that might cause a problem with your macro -- the rst setting with "06to14waters to match my table nametot".  This needs to be the actual table name.  Perhaps you need to create the table name in code, getting some component from elsewhere.
If you have GoToAssist or some other remote connection app, you could send me an invitation so I could see your computer.  My email is hfeddema@hvc.rr.com.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249716
At present, my computer that does email is down, so you would need to tell me the necessary logon info here.  Or I could start a session and give you the logon info.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249726
Here is a link to join a GoToAssist session:
https://www.fastsupport.com/866006874
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249736
Did you try the link?  The GTA window says "Customer declined to join this session"
0
 

Author Comment

by:Cam Raben
ID: 40249738
Thanks Helen.  I'm not allowed to allow an outside party to connect to my machine here at work.  I appreciate your efforts and will keep plugging away with your suggestions.  : )
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249743
0
 

Author Comment

by:Cam Raben
ID: 40249746
Here is the macro with the typo corrected:   This is the version I am using.  My table name is 06to14waterstot.


Public Sub AddElementsToTable()

   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim strElement As String
   Dim tdf As DAO.TableDef
   Dim strSQL As String
   
   Set rst = CurrentDb.OpenRecordset("06to14waterstot")
   Set tdf = CurrentDb.CreateTableDef("tblElementsPlus")
   
   Do While Not rst.EOF
      strElement = rst![Element]
      Debug.Print "Element: " & strElement
      tdf.Fields.Append tdf.CreateField(Name:=strElement, Type:=dbDouble)
      rst.MoveNext
   Loop
   
   tdf.Fields.Append tdf.CreateField(Name:="Element", Type:=dbText)
   CurrentDb.TableDefs.Append tdf
   
   Set rst = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   
   strSQL = "INSERT INTO tblElementsPlus ( Element )" _
      & "SELECT 06to14watertot.Element FROM 06to14watertot"
   CurrentDb.Execute strSQL, dbFailOnError

End Sub
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249750
Try editing the table name in the rst setting statement to exactly match a table name in your database.  That should work.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249751
Or if you want to try one more thing, save the database in .mdb format and I will see if I can download that.
0
 

Author Comment

by:Cam Raben
ID: 40249771
Here's the 2003 mdb version.
TESTDWR-WQdata06to14---Copy1.mdb
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 1800 total points
ID: 40249836
I was able to download the .mdb database.  I found two problems -- a typo in the table name (easily fixed), and duplicate entries in the Element field, so that the code tried to create numerous fields with the same name, which is not allowed.  I made a query with unique values for Element, and used that instead of the table, and the code created tblElementsPlus correctly.  Here is the modified database.
TESTDWR-WQdata06to14-HBF-Modified.mdb
0
 

Author Comment

by:Cam Raben
ID: 40249858
Great.  I also need to join the fields from both of these tables.  Is that done using a vba macro as well?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249889
No, generally fields are joined in the Relationships diagram.  Your 06to14watertot table has an Autonumber field, ID, which could be designated as the key field, and then it could be joined to other tables that have a matching ID field as a foreign key field.  tblElementsPlus doesn't have a matching field, so it couldn't be joined to 06to14watertot.  What is the purpose of the joins you want to make?
0
 

Author Comment

by:Cam Raben
ID: 40249894
I need all fields in one table, and I need it to be automated because I am doing this same process for several different dbs.  It is imperative that all fields (from both tables) be in one table because the element fields have no meaning to me without the station, date, and other information being coupled to them.  (I would have added the fields by hand but I will be doing this for multiple dbs.)
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40249895
I think the original question has been answered -- can you post the joins issue as a new question?
0
 

Author Comment

by:Cam Raben
ID: 40249899
Ok, thanks very much Helen.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

864 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