Create new table (1 Viewer)

[kc1]

Create new table using another tables data as field headings

Hi

I need to create a new table, for the field headings I want to use the data in another table.

The only way I can think of is exporting to Excel, transposing, then importing back into Access, but I would prefer to keep it in Access.

Can this be done without using Excel?

Thanks in advance

 

[MSAccessRookie]

Re: Create new table using another tables data as field headings

Hi

I need to create a new table, for the field headings I want to use the data in another table.

The only way I can think of is exporting to Excel, transposing, then importing back into Access, but I would prefer to keep it in Access.

Can this be done without using Excel?

Thanks in advance

Can you provide additional information concerning your need for creating a new Table? Although I am sure that there is a way to do what you want, I am equally sure that there might be a way to accomplish what you want without creating a new Table.

 

[kc1]

Re: Create new table using another tables data as field headings

Can you provide additional information concerning your need for creating a new Table? Although I am sure that there is a way to do what you want, I am equally sure that there might be a way to accomplish what you want without creating a new Table.

Thanks for the reply.

Table1 - there is one field, Date, the amount of records will vary.

New Table to be created - Field Headings will consist of the dates in Table 1

Does this help?

 

[MSAccessRookie]

Re: Create new table using another tables data as field headings

Thanks for the reply.

Table1 - there is one field, Date, the amount of records will vary.

New Table to be created - Field Headings will consist of the dates in Table 1

Does this help?

Not exactly. Is there a need to store data that will be constantly changing in a Table? If you need it for a Report, then a Query that calculates the required results could be the source for the Report. As a general rule, you should not store calculated data in a Table, and instead you allow a Query (which can be treated like a Table) to handle the data for you.

 

[kc1]

Re: Create new table using another tables data as field headings

Not exactly. Is there a need to store data that will be constantly changing in a Table? If you need it for a Report, then a Query that calculates the required results could be the source for the Report. As a general rule, you should not store calculated data in a Table, and instead you allow a Query (which can be treated like a Table) to handle the data for you.

It is not as complicated as this.

I just need a new table, blank with no records, I will populate the table later in my process.

My best bet now is to export Table1 into Excel, transpose Column A in to Row 1 then import back into Access.

 

[Rabbie]

Have you considered using a maketable query? BTW not a good idea to have a column called Date. Date is a reserved word in Access

 

[dcb]

I am, unlike MSAccessRookie, too SCARED to ask why... You are looking to do something like this....

Public Sub Make_A_Table_To_Scare_Normalised_People()
    Dim rst As Recordset
    Dim db As Database
    Dim sqlDrop As String
        sqlDrop = "DROP TABLE yourtablename;"
    Dim sqlCreateTable As String
        sqlCreateTable = "CREATE TABLE yourtablename (ID AUTOINCREMENT);"
    Dim sqlAlterTableS As String, sqlAlterTableE As String, sqlCreateTableM As String, sqlAlterTable As String
        sqlAlterTableS = "ALTER TABLE yourtablename ADD COLUMN ["
        sqlAlterTableE = "] Char;"
    Dim sqlOrigin As String
        sqlOrigin = "SELECT DISTINCT * FROM Table1;"

    ' DoCmd.RunSQL sqlDrop
    DoCmd.RunSQL sqlCreateTable

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sqlOrigin)
    Do Until rst.EOF
        sqlAlterTableM = CStr(rst.Fields(0))
        sqlAlterTable = sqlAlterTableS & sqlAlterTableM & sqlAlterTableE
        DoCmd.RunSQL sqlAlterTable
        rst.MoveNext
    Loop
End Sub

PS. Just watch out in SQL Autoincrement does not apply constraints or indexes

 

[kc1]

Have you considered using a maketable query? BTW not a good idea to have a column called Date. Date is a reserved word in Access

Rabbie

I think you are missing the point, when creating a new table the field headings will vary each time, the headings will be derived from another table and I need this to be automated, so a simple Make Table Query will not do.

I have no field heading called date, it was just an example.

Thanks anyway

KC

 

[Rabbie]

Thanks for clarifying that. If someone posts

Table1 - there is one field, Date, the amount of records will vary.

I tend to thnk that's wat they have - foolish I know I must learn that people don't always post the correct infomation

 

[kc1]

I am, unlike MSAccessRookie, too SCARED to ask why... You are looking to do something like this....

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Sub Make_A_Table_To_Scare_Normalised_People()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim rst As Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim db As Database[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim sqlDrop As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlDrop = "DROP TABLE yourtablename;"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim sqlCreateTable As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlCreateTable = "CREATE TABLE yourtablename (ID AUTOINCREMENT);"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim sqlAlterTableS As String, sqlAlterTableE As String, sqlCreateTableM As String, sqlAlterTable As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlAlterTableS = "ALTER TABLE yourtablename ADD COLUMN ["[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlAlterTableE = "] Char;"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim sqlOrigin As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlOrigin = "SELECT DISTINCT * FROM Table1;"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    ' DoCmd.RunSQL sqlDrop[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    DoCmd.RunSQL sqlCreateTable[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Set db = CurrentDb[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Set rst = db.OpenRecordset(sqlOrigin)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Do Until rst.EOF[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlAlterTableM = CStr(rst.Fields(0))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        sqlAlterTable = sqlAlterTableS & sqlAlterTableM & sqlAlterTableE[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        DoCmd.RunSQL sqlAlterTable[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        rst.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Loop[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub

PS. Just watch out in SQL Autoincrement does not apply constraints or indexes

Thanks DCB

I will give you code a go.

Good question, why am I doing this.......
I have a dataset, within this dataset there is a field called TimesheetDate. This ranges from 01/06/09 to 12/12/09, but this will change as time goes on.
I need to create a final calculation output, which has the TimesheetDate as the headings.

If the heading did not change then this would very simple, however, as the dates are dynamic it is a lot more complex.

I could of done this exporting to Excel, transposing, then importing back but I knew there must of been a way of doing this in Access.

I will try your code now.

Many thanks

KC

 

[dcb]

Give it a go - NOTE the commented line for the drop table - ie this will only work once unless you uncomment the line - You should be dropping a temp table anyway after use

 

[kc1]

Thanks for clarifying that. If someone posts
I tend to thnk that's wat they have - foolish I know I must learn that people don't always post the correct infomation

Yes, people dont tend to go into specifics, not really crucial to know what the field is called etc when asking a complex question. No need to go down to that level otherwise the question will go on forever!!

Thanks

KC

 

[kc1]

Give it a go - NOTE the commented line for the drop table - ie this will only work once unless you uncomment the line - You should be dropping a temp table anyway after use

Thanks

It works, however after 7 loops, I get an error message:
Record is too Large

Any ideas?

 

[dcb]

Thanks

It works, however after 7 loops, I get an error message:
Record is too Large

Any ideas?

Err.Number?
Which line does the debugger go to?
I will test here

 

[dcb]

Found it:
The Char statement in AlterTableE defaults to Char(255) change this to Char(10) or whatever is suitable for your end application

sqlAlterTableE = "] Char(10);"

 

[kc1]

Err.Number?
Which line does the debugger go to?
I will test here

error 3047
line - DoCmd.RunSQL sqlAlterTable

 

[dcb]

Also try sqlAlterTableE = "] TEXT;" and see if you hit the limit....

 

[kc1]

Also try sqlAlterTableE = "] TEXT;" and see if you hit the limit....

2 words.....
absolute genius

works like a dream

many thanks

 

[boblarson]

Why not just build a dynamic SQL string and use an Insert query? Seems like it would be easier.

 

[boblarson]

Well, nevermind, if you got it to work