Dynamically Add fields to a table

[jamez05]

I'm using Access 2003. I have a query that holds site ids, chemicals and the chemical's BSAF:

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:

SiteID Chem1  Chem 2
  1    .0001   .0002

Is there a good way to handle this?

Thanks

James

 

[Remou]

I am not exactly sure what you wish to achieve, but have you considered crosstab queries?

 

[TheAceMan1]

How are ya jamez05 . . .

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

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jamez05]

Hi everyone,

Will get back when I figure this out.

 

[Remou]

Have a look at crosstab queries.

 

[JoeAtWork]

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

 

[jamez05]

Hi, thanks for the input, here's what I ended up doing and it works fine:

    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

 

[JoeAtWork]

However, you are now creating tables that break the foundamental rules of database normalization!