Access Crosstab -> Excel via CF table.

I have an Access Crosstab query that I need to output into a ColdFusion table directly exported into Excel via cfheader/cfcontent.
I need to output a table with dynamic column headers because the crosstab query sometimes will only have 1 column, and sometimes it will have ~10 columns depending on the data.  

ie)

Office  |  Cat1  | Cat2  | Cat3
----------------------------------
Loc1    |  744   | 7664 | 273
----------------------------------
Loc2    |  346   | 2323 | 2322
----------------------------------
etc, with Cat1, Cat2, Cat3... being dynamic based on the query.
RussoMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mosphatCommented:
There's a variable cold queryname.columnlist you can use.
It would be something like this:

<table border="1">
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <cfloop list="#queryname.columnlist#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0
RussoMAAuthor Commented:
And column headings to match the query?
0
RussoMAAuthor Commented:
also i want to be able to have the office column always be the leftmost column, there will always be data in that column...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

mosphatCommented:
You mean something like this?

<table border="1">
  <tr>
    <th>Office</th>
    <cfloop from="1" to="#listLen(queryname.columnlist)#" index="i">
      <th>Cat#i#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <td>Loc#i#</td>
    <cfloop list="#queryname.columnlist#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0
RussoMAAuthor Commented:
actually i want to be able to pull in the column headings from the query itself..

they are things like:

InventoryError, WasteError, BillingError, etc...

i want to have this be as little formatting as possible after exporting the table.
0
mosphatCommented:
Replace this:
<cfloop from="1" to="#listLen(queryname.columnlist)#" index="i">
      <th>Cat#i#</th>
    </cfloop>

with this:
<cfloop list="#queryname.columnlist#" index="column">
      <th>#column#</th>
    </cfloop>
0
RussoMAAuthor Commented:
so how do i make the office column always be the leftmost column.  everything else good so far.  office is the leftmost column in the crosstab query...
0
mosphatCommented:
Ow, now I understand what you mean. I think...

<cfset officeIndex = listFindNoCase(queryname.columnlist, "office")>
<table border="1">
  <tr>
    <th>Office</th>
    <cfloop list="#queryname.columnlist#" index="column">
      <cfif i neq officeIndex>      
        <th>#column#</th>
      </cfif>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <td>#queryname[column][officeIndex]#</td>
    <cfloop list="#queryname.columnlist#" index="column">
       <cfif i neq officeIndex>
      <td>#queryname[column][i]#</td>
      </cfif>
    </cfloop>
  </tr>
</cfloop>
</table>
0
RussoMAAuthor Commented:
i think that will do it, i will check it first thing tomorrow, already off that for the time being.
0
mosphatCommented:
This is a little more efficient.
It makes sure the 'office' column is first in line.

<cfset officeIndex = listFindNoCase(queryname.columnlist, "office")>
<cfset list = listAppend(listGetAt(queryname.columnlist, officeIndex), listDeleteAt(queryname.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <cfloop list="#list#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RussoMAAuthor Commented:
last one seems to do the trick... BUT it does it twice?:

OFFICE                 BILLINGERROR
Summerside          4
Dryford                 300

OFFICE                 BILLINGERROR
Summerside          4
Dryford                 300
0
mosphatCommented:
It's not my code that is duplicating.
What else did you code around it?
0
RussoMAAuthor Commented:
verbatim:

<cfoutput query="showresults">

<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset list = listAppend(listGetAt(showresults.columnlist, officeIndex), listDeleteAt(showresults.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i">
  <tr>
    <cfloop list="#list#" index="column">
      <td>#showresults[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>

</cfoutput>
0
mosphatCommented:
Get rid of the query="showresults" in <cfoutput query="showresults">. The query is already outputted in this part: <cfloop from="1" to="#showresults.recordcount#" index="i">
0
RussoMAAuthor Commented:
thanks, that was it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.