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

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.

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:)

...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

@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

Apparently a "transform" sql statement can do this....?

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

Select allOpen in new window

The sample database is attached.
Elements-Test.accdb

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?

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?

I could save the database in the .mdb format if you want to try that.

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.

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.

I added these two lines after the Declarations section to delete tblElementsPlus if it exists:

On Error Resume Next
   DoCmd.DeleteObject acTable, "tblElementsPlus"   

Select allOpen in new window

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

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

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

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.

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.

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

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

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.

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.

Here is a link to join a GoToAssist session:
https://www.fastsupport.com/866006874

Did you try the link?  The GTA window says "Customer declined to join this session"

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.  : )

Here is another link:
https://www.fastsupport.com/732504482

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

Try editing the table name in the rst setting statement to exactly match a table name in your database.  That should work.

Or if you want to try one more thing, save the database in .mdb format and I will see if I can download that.

Here's the 2003 mdb version.
TESTDWR-WQdata06to14---Copy1.mdb

Great.  I also need to join the fields from both of these tables.  Is that done using a vba macro as well?

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?

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.)

I think the original question has been answered -- can you post the joins issue as a new question?

Ok, thanks very much Helen.