Problem inserting columns into a table

[Steveanderson]

I wrote a little procedure in order to add new colunms to a table
Table listedemog is a list of the columns to add
Column name: Demog
Row data: a b c d

Table Demog is the table containing all the data
Column names: a b c d e f g

Table DemogSelect is theresult table with only the columns from listedemog and the data from the demog table. ( Note: The #Req is added by default in the begining of the procedure)

The procedure will create my new table with the right columns but without any data in the columns

what am I doing wrong ?


Private Sub Commande34_Click()
Dim DB As Database
Dim Rst As Recordset
Dim NumRecords As Long
Dim M As String

DoCmd.RunSQL "SELECT Demog.[#Req] INTO DemogSelect FROM Demog;"

Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("listedemog")
If Rst.RecordCount = 0 Then
Exit Sub
End If

Rst.MoveFirst
M = Rst!Demog
DoCmd.RunSQL "ALTER TABLE DemogSelect ADD COLUMN [" & M & "] TEXT(10);"
DoCmd.RunSQL "INSERT INTO DemogSelect SELECT Demog.[" & M & "] FROM Demog;"
Do While Not Rst.EOF
Rst.MoveNext
M = Rst!Demog
DoCmd.RunSQL "ALTER TABLE DemogSelect ADD COLUMN [" & M & "] TEXT(10);"
DoCmd.RunSQL "INSERT INTO DemogSelect SELECT Demog.[" & M & "] FROM Demog;"
Loop


End Sub

 

[boblarson]

Why are we adding columns to a table? What data goes in this table? Most of the time, when you design your databasse table structure you should have it normalized as such so that you add ROWS of data to tables and NOT columns.

 

[Steveanderson]

Hello Bob,
I'm running access 2003

Here is what I'm trying to do and maybe I'm doing it wrong:
I have a huge table with at least 200 coulmns of data. I want to create an interface which allows the user to save a new table containing only the columns that he selected.
I created a form with a combobox which displays a list of the names of the columns from a vertical table). Selecting the name will add it to a new table (vertical).
I want to use the name of the fields of this new table to create a results table which links to my original data.

I don't know if that's clear but I don't see any other way to do it.

 

[ghudson]

I would design a process to allow the user to select which fields they want to query on instead of creating a table. Is your data normalized if you have 200 fields in one table?

 

[The_Doc_Man]

I'm with GHudson on this one. I have worked with U.S. Dept. of Defense databases for over 20 years. Those databases are NOTORIOUS for bloat-factor. I have yet to see a legit, normalized database with 200 fields in a single record.

This sounds to me like an Excel person tried to make an Access database but left on their Excel thinking cap. Having a very wide structure like this is common to Excel thinking. What you want for Access is something narrower in structure with more records and fewer fiedls. Perhaps with some tables split out for lookups via JOIN, but in general just a lesser amount of data per record.

Once you do that, some queries can be built more easily. And queries are definitely the way to go.