crosstab questions

[Michael S. Montoya]

Ok...I have setup a crosstab query. That part was fairly easy using the
wizard. Not the hard part is how to output the data!!!

A report's controls need to be bound to fields. However, with the crosstab,
the field names change when the groupings (column headings) change. How do
you tackle this?

 

[Duane Hookom]

There are a couple different methods. Check the samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

If your column headings are date/time intervals, there may be a much easier
solution.

 

[Michael S. Montoya]

To be honest, I have seen this sample, but have yet to understand how to get
it to work for my application. No, the column headings are not dates, they
are basically suppliers used within the given time period.

I believe my final result would be best in Excel because even in Landscape,
they will never fit across a page. I don't want the user to have to create
pivot tables in excel (I want it as automated as possible).

What is the VBA code to loop through the field names of a query? Could this
be a method?

 

[Duane Hookom]

If you want to push the query to Excel then you shouldn't need field names.
However:
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Set db = CurrentDB
Set rs = db.OpenRecordset("qxtbYourQuery")
For Each fd in rs.Fields
debug.print fd.Name
Next
rs.Close
Set rs = Nothing
Set db = Nothing

 

[Jamie Collins]

I believe my final result would be best in Excel
What is the VBA code to loop through the field names of a query? Could this
be a method?

Probably the easiest to way to create a new Excel table is to use the
SELECT..INTO syntax e.g. if you query looked like this:

SELECT MyTextCol
FROM MyTable
;

then add an INTO clause like this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].MyNewExcelTable
FROM MyTable
;

And if you just wanted the column headers, add a WHERE clause like
this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook].MyExcelTable
FROM MyTable
WHERE 0=1
;

For an existing Excel table, use the INSERT INTO..SELECT syntax.

Jamie.

--