Help Stamp Out Brain-Death: Cross-Tab to Make Table

[raskew]

Hi -

Desperately need a kick in the butt to restart the cognitive functions.

Am attempting to prepare a FY (1 Jul 05 - 30 Jun 06) rollup of services provided. Have done this for years with very little stress. Lost my notes in a melt-down and now it seems I'm lost.

Situation:

Properly normalized recap of various (about 15) services provided to clients over the one-year period. Created a cross-tab query that sums each of the services and displays columns for each service (represented by ServiceID, e.g. 2019, 2020, etc.)

The cross-tab works perfectly, just now need to turn it into a report. Try to use the Report Wizard, based on the cross-tab. Problem is, it returns no fields to select. Changed strategy and attempted to turn the cross-tab into a make-table query, which, I understand would return a spreadsheet-style table. For these purposes, that's exactly what I need and have examples going back 5 to 6 years how this worked wonderfully.

The cross-tab -> maketable works, but it reverts to the original normalized structure, which is not what is needed.

If someone can help to put me out of my misery, I offer my first-born as a small token.

Thanks,

Bob

 

[The_Doc_Man]

How old is the first-born? Still in diapers?

Usually the problem is layering because a cross-tab query will create its own field (column) names and row (record-key) names. You have to know the field names ahead of time to build a report and the SQL associated with the cross-tab is ... how shall we say this politely? ... screwy.

Normally a query contains field names for inclusion into your recordset. However, the cross-tab query contains ROW names and lets field values provide the column breaks. Which is why I described it as I did.

To make a report of out this might be tricky. Any chance of making it an embedded sub-form (datasheet view) in the report?

 

[JoeCruse]

Bob,

won't you have to define column headings in the properties of the crosstab query to get any headers for the report?

 

[raskew]

Joe & Doc

Thanks for your responses. Out of desperation and on a tight suspense, ended up populating a temp table using code (the cross-tab works just fine--absolutely as advertised--as a recordset) and using the table as the report source. I'm still mystified as I have literally dozens of reports based on cross-tab queries, but this one just wouldn't fly. I'm lost on this -- have I been lucky all these years and now it's time to pay the reaper?

Will look at the column naming situation in the morning. What you're saying makes sense and I appreciate your input!

Thanks again - Bob