extract list of table fileld names

[newcoder54]

How do I extract a list of table field names and put them into a different table?

 

[dhookom]

How do you want to "put them into a different table"? Do you want to create a new table with a similar structure or should the field names become data values?

Is this a one-time event or do you want to do this repeatedly?

You can copy fields from one table design view to another. You can also copy a table and paste it without the records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newcoder54]

I need to keep an ongoing list of field names from a table that it created from a crosstab query. The crosstab is constantly changing as most fields are dates that roll in and out. I need to put the field names into a table and do not need the data. That way I can keep track of activity as dates change in the crosstab query. The number of fields can vary. I see how to create a table of table and query names but not field names from a particular table. Thanks,

 

[dhookom]

The columns in a crosstab are derived from a GROUP BY clause in a select query. You should be able to create a query similar to your crosstab but not a crosstab. Use the results as an append query to your "List of Fields" table.

If you can't figure this out, come back with the SQL view of your crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

Would this help?

Public Function ListTableFields(tbl As String, Optional dbs As String)
Dim db As DAO.Database          'Database
Dim myTable As DAO.TableDef     'TableDef
Dim fld As DAO.Field            'Field
Dim x As Integer                'Count of fields
x = 0

    If dbs = "" Then
        Set db = CurrentDb
    Else
        Set db = OpenDatabase(dbs)
    End If
        
    Set myTable = db.TableDefs(tbl)
        x = myTable.Fields.Count
     
     With myTable
        For Each fld In .Fields
            Debug.Print " " & fld.Name
        Next fld
    End With
End Function

Example in immediate window:
?listtablefields("tblMyTable")

~Melagan
______
"It's never too late to become what you might have been.

 

[JoeAtWork]

Since it's a cross-tab query I don't think it will be in the TableDefs collection.

You could just open a recordset of the query and get the field names from that:

rs.Fields(0).Name

 

[Melagan]

How do I extract a list of table field names and put them into a different table?

OP did not mention anything about queries.


~Melagan
______
"It's never too late to become what you might have been.

 

[JoeAtWork]

field names from a table that it created from a crosstab query

Melagan - my apologies, I misread that statement.

 

[newcoder54]

Thanks for the help guys. Maybe I should simplify this bit

I scraped this up and it works fine but I need to write the output to a storage table with field names of field1, field2 etc. I will insure that the number of fields coming out out this ins less than my storage table. Hope that helps.

Function fieldToMessageBox()

Dim rst As Recordset
Dim f As Field

Set rst = CurrentDb.OpenRecordset("Table1")

For Each f In rst.Fields
MsgBox (f.Name)
Next
rst.Close

End Function

 

[dhookom]

Do you really need code? Did you consider using the method I suggested? Could you share your SQL view of your crosstab? Do you want ever field from the crosstab or only the ones derived from the Column Headings?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newcoder54]

Here is the SQL Duane,

TRANSFORM Sum(test1.Minutes) AS SumOfMinutes
SELECT test1.REPNAME, Sum(test1.Minutes) AS [Total Of Minutes]
FROM test1
GROUP BY test1.REPNAME
PIVOT test1.MyDate;

This puts out several date columns when run


As the dates change in the fields change daily, I need to appned the new field naames to a FieldStorage Table. That code from one message back that writes the fields to a message box is just fine but I need to keep it in a table. Hope that explains it a bit better!

 

[PHV]

A starting point (SQL code):
INSERT INTO [a table] ([field name]) SELECT DISTINCT MyDate FROM test1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH, hit exactly what I was going for without using any code. If you need every column from the crosstab, you would use:

=== quniResults ===
SELECT MyDate FROM test1 
UNION
SELECT "REPNAME" FROM test1
UNION
SELECT "Total Of Minutes" FROM test1;
============

Then use this union query in an append or make table query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newcoder54]

Thanks a lot to both of you! Can't belive such a few lines does so much. Guess I need to learn a bit union queries - and maybe so know what I am after. Now, how do I put that back into a row with the top row going to field 1, row 2 to field 2 etc? The end result is that need the headers into a table. Make sense?? Thanks!

 

[dhookom]

If I still understand correctly, as stated

Then use this union query in an append or make table query.

I'm not sure I understand what you mean by "row going to field". It would help tremendously if you would take the time to type in some sample before and after records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newcoder54]

Hi Duane,

Yes, good idea, Here is the output of the cross tab showing only 2 dates instead of all 7 so it fits the box here. The crosstab query will be run again in a week and those field names of dates will change

Rep Minutes 5/1/2007 5/2/2007
LAURA 1554 120 119
LORI 2096 117 117
NICOLE 10238 403 339

Here is what the table I need would look like after 3 weeks. Again this is only 2 of the 7 days. The field names including Date 1 and Date 2 would not change but the data which is the field names from the above crosstab would change. This is done for many groups or I would simply cut and paste. Well, I have at least explained something to myself!!

Wk end Date 1 Date 2
05/08/07 5/1/2007 5/2/2007
05/15/07 05/09/07 05/10/07
05/22/07 05/16/07 05/17/07

 

[dhookom]

It looks like all of this is very structured. Are there going to be missing dates or varying numbers of columns?

I don't know what the purpose of your second table is. If you have the Wk End date, you can calculate all the other columns if needed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newcoder54]

This is for documentation work. Dates could vary but the number of columns wont. Another way to put it is that put the field name of the third column of the crosstab table into a data row, of the field storage table, that has a field name of Field 3 etc etc. A one to one correspondence.

 

[dhookom]

I couldn't justify doing this since it would create an un-normalized table structure. I would rather put the field names into a single column like:
[tt]
WkEnd TheDate
======== ========
05/08/07 5/1/2007
05/08/07 5/2/2007
05/08/07 ......
[/tt]

I can't bring myself to support creating a worksheet without proper justification. You can always take the normalized table and create your second table as a query.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]