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

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.
Cam RabenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
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
PatHartmanCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
...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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Cam RabenAuthor Commented:
@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
Cam RabenAuthor Commented:
Apparently a "transform" sql statement can do this....?
0
Helen FeddemaCommented:
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
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
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
Helen FeddemaCommented:
I could save the database in the .mdb format if you want to try that.
0
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
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
Helen FeddemaCommented:
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
Cam RabenAuthor Commented:
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
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
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
Helen FeddemaCommented:
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
Cam RabenAuthor Commented:
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
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
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
Helen FeddemaCommented:
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
Helen FeddemaCommented:
Here is a link to join a GoToAssist session:
https://www.fastsupport.com/866006874
0
Helen FeddemaCommented:
Did you try the link?  The GTA window says "Customer declined to join this session"
0
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
0
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
Try editing the table name in the rst setting statement to exactly match a table name in your database.  That should work.
0
Helen FeddemaCommented:
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
Cam RabenAuthor Commented:
Here's the 2003 mdb version.
TESTDWR-WQdata06to14---Copy1.mdb
0
Helen FeddemaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cam RabenAuthor Commented:
Great.  I also need to join the fields from both of these tables.  Is that done using a vba macro as well?
0
Helen FeddemaCommented:
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
Cam RabenAuthor Commented:
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
Helen FeddemaCommented:
I think the original question has been answered -- can you post the joins issue as a new question?
0
Cam RabenAuthor Commented:
Ok, thanks very much Helen.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.