Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamically Add fields to a table

Status
Not open for further replies.

jamez05

Programmer
Jul 29, 2005
130
US
I'm using Access 2003. I have a query that holds site ids, chemicals and the chemical's BSAF:
Code:
SiteID Chemical BSAF
  1     Chem1   .0001
  1     Chem2   .0002
I need to dynamically create a table based off this query that adds the chemical as a field so the table would end
up being like:
Code:
SiteID Chem1  Chem 2
  1    .0001   .0002

Is there a good way to handle this?

Thanks

James
 
I am not exactly sure what you wish to achieve, but have you considered crosstab queries?
 
How are ya jamez05 . . .

Looks like [blue]Excel[/blue] to me! . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see FAQ219-2884:
 
Hi everyone,

Will get back when I figure this out.

 
I second Remou's suggestion, this is exactly would crosstab queries are for.

 
Hi, thanks for the input, here's what I ended up doing and it works fine:
Code:
    Dim qryProbAll As String
    Dim qryProbSite As String
    Dim ProbChem As String
    Dim tblProbabilityPlot As Object
    Dim ColName As Object
    Dim db As Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()

    Set rs = CurrentDb.OpenRecordset(qryProbAll)
    If rs.RecordCount  Then              
      DoCmd.DeleteObject acTable, "tblProbabilityPlot"
       Set tblProbabilityPlot = db.CreateTableDef("tblProbabilityPlot")
       Set ColName = tblProbabilityPlot.CreateField("BSAF", dbDouble)
       tblProbabilityPlot.Fields.Append ColName
       db.TableDefs.Append tblProbabilityPlot
                    
                    
       ProbChem = ""
       rs.MoveFirst
       Do Until rs.EOF
           If ProbChem <> rs!Chemical Then
               Set ColName = tblProbabilityPlot.CreateField(rs!Chemical, dbDouble)
               tblProbabilityPlot.Fields.Append ColName
               ProbChem = rs!Chemical
           End If
           strSQLInsert = "INSERT INTO tblProbabilityPlot(BSAF,[" & rs!Chemical & "]) Values ("
           strSQLInsert = strSQLInsert & rs!BSAF & "," & rs!BSAF & ")"
           DoCmd.RunSQL strSQLInsert
           rs.MoveNext
         Loop
         rs.Close
      End If
 
However, you are now creating tables that break the foundamental rules of database normalization!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top