Capturing Crosstab Column Names

[jkfeagle]

Looking for some help on this one. I have a database that someone else created and I was asked to tweak it. Problem is it needs major overhauling. Short term however, I can solve their problem by some minor mods. I hit a snag however where a crosstab query is concerned. I need to capture the column headers (they are variable and will change each week) in order to use them as values in a single field in a table. Does anyone know how to do this by chance? Any help would be greatly appreciated.

 

[pitou]

crosstab query

Hello JFKeagle

I read your question.
I can give you neither a solution with SQL nor with VBA, but what seems to me possible is to resort to EXCEL.
The outcome of your crosstab query could be exported directly to EXCEL (tools/ office links) then, after to re-integrate the tab of the excel sheet into an Access table.

You will have perheaps to re-structure the tab on excel and to let the head columns thus it is possible to have all your variables.

This piece of advise is suitable if you have to do your overhauling only one time.
If you have to retrieve the datas of your cross table each two days humm ... it should be good, for instance to make appear the outcome of your query directly on excel, to create a macro (you can do that easily by being assisted without typing the code) that will copy and paste, aside, a tab, this tab meant to be imported in Access.

I m sorry it is not a complete solution, but hope it will help to design the final and good one. reply if necessary

Jean-Pierre

 

[CJBIRKIN]

Hello

This sub will return all of the fields in a table to an array. You need to put it in a module.
' put this at the top of the module
' it will allow you to access the fieldnames from another sub
public Fieldnamed() As String

Public Sub gettablecolnames()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim rst As DAO.Recordset
Dim Fieldcount As Integer
Dim count As Integer

' set the database to the current database
Set db = CurrentDb
'set the rst = to the table in question
Set rst = db.OpenRecordset("TBL_STAFF")

' count the number of fields
Fieldcount = rst.Fields.count


' the first field is 0 so i've set the array size to 3 (0,1,2,3)

fieldcount = fieldcount-1

' set the array to the number of fields
ReDim Fieldnamed(Fieldcount)
count = 0

' loop through the fields each one being returned to the array
For count = 0 To Fieldcount
Fieldnamed(count) = rst.Fields(count).name
Next count


End Sub

The code may not be the most elegant but it works!!
An questions feel free

Chris