subform based on crosstab?

[jatfill]

this is the error message I get:
"You can't use pass-through query or non-fixed-column crosstab query as a record source for a subform or report. - Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

Any help someone cn offer would be much appreciated... I created a crosstab query to pull the following:

TABLE data:

contactID I1
contactID A2
contactID B7
contactID C5
contactID D8


Crosstab results:

contactID I1 A2 B7 C5 D8

those values are being pulled from a session registration table, and I need them to appear exactly this way on a nametag at the bottom (I have limited room) The number of sessions is dynamic (for example, an "E" session may be added later, so I can't manually specify the column headings, don't even use the headings on the report, I just insert the values into a report... but I get the exact same error message when I try to attach it as a subform to the form with the contact name, etc. on it

Is this an impossibility? The only solution I can think of is creating a table based on the query results before the report is run, but that would really be a horrible nightmare to pull off. heeeelp!!

 

[jatfill]

I was thinking about that part of it earlier, and I came up with the idea of reversing the subform/parent form relationship...? It's definitely worth a try, thank you very much for the input!

 

[Rich@ITTC]

Hi Jatfill

Just some re-assurance ... I struggled with doing an invoice with the invoice items being based on a crosstab query that fed into a subreport. Result? Couldn't get it to work.

However, once I made the crosstab the query underlying the main report (and brought in other invoice details like customer name and address details in subreports) it worked like a dream!

Lateral-thinking with Access - that's often what is required!

Rich Gorvin

 

[jatfill]

Thanks, it sounds like I'm on the right track!

While we're on the subject, will the results of the crosstab report be dynamic? As I eluded to in my original message, the number of columns in the crosstab are/will be dynamic, depending on the event itself. So there might be three columns in the crosstab query results, there might be 10, dependingon the event definition setup. Does that require the report itself to be rebuilt when the columns change,or does Access handle that? If it can't, I assume I need to set up a max group limit, unless anyone has a suggested workaround?

Wow, when I started this portion of the design (reporting) I thought I was home free, but I am sadly mistaken, I guess... thanks again!

 

[KevinM]

Crosstab queries and reports certainly seem to be 'flaovour of the month' at the moment.

God knows why, they are extremely unwieldy in reports and are nothing more than a chart.
I wouldn't recommend using them in reports because no they are NOT dynamic like the crosstab query itself.

Try using Pivot Tables instead where you will be able to format the table how you like and it will always remain dynamic.

From experience, I have found that people require far too much from Access and don't make full use of the MS Office suite.

If you want crosstab reports then use Excel.

If you want fancy reports and labels then use MS Word.

If you want complex e-mail and scheduling procedures then use Outlook.

 

[jatfill]

those are all good points... and I wish I could say that this is going to be a major feature within the database I'm working on, but it's not. It's really one of those insignificant things that no one will ever appreciate unless it doesn't work.

As far as using other Office tools, I'm well versed in Word/Excel, but I will not be the person who is doing any of the actual data entry and printing, etc. from this db, and as I think a lot of people experience, my users are really only capable of "point and click" usage... if I tell them to make nametags in Word they would be totally lost, and I'd wind up doing it anyway. If this was simply a tool for myself, I'd write it all on the fly and be done with, just as you suggest.

Access supports labels and nametags, I honestly don't think it's too much to ask of the software to have them pre-formatted, I'm working on it now and when I'm finished I will know that much more about how to set things like this up.