Forms and Reports from CrossTab queries

[John Dumay]

Hi All,

I have read a few postings in this area, but they all seem to be very
specific to particular situations. Can someone please guide me towards the
best solution to my dilema?

I have a form with two list boxes which contain many variables (answers to
specific questions from a survey). When two different questions are slected I
run a cross tab query resulting in a dymanic result of rows and columns based
on the selections.

I now want to display the result as a datasheet on my form and in a similar
fashion as a report.

Any help is, of course, greatly appreciated.

Regards,

John Dumay

 

[Krzysztof Pozorek [MVP]]

(...)

I now want to display the result as a datasheet on my form and in a
similar
fashion as a report.

You can choose simple way or difficult...

The simple one is such:
1. Put subform on Your form
2. As SourceObject in Your subform set:
Query.YourCrossTabQuery
3. That's all ;-)

The difficult way... Hmmm...
This simple one will suffice You maybe? ;-)

K.P.
www.access.vis.pl

 

[John Dumay]

Hi K,

thanks for the input. this works fine for the current cross tab result, but
when I change the cross tab to different varaibles I get errors because the
field and row names are no longer the same. See below.

How can i make this process dynami so it updates the form with the proper
header and row names?

Regards,

John

Row_Category A job is no longer a necessity for me Financially secure It is
a real struggle Must have a job in order to make ends meet Must have a job so
I or we can get ahead Wealthy
Arrogant #Name? #Name? #Name? #Name? #Name? #Name?
Distant #Name? #Name? #Name? #Name? #Name? #Name?
Neutral #Name? #Name? #Name? #Name? #Name? #Name?

 

[Krzysztof Pozorek [MVP]]

(...)

thanks for the input. this works fine for the current cross tab result,
but
when I change the cross tab to different varaibles I get errors because
the
field and row names are no longer the same. See below.

How can i make this process dynami so it updates the form with the proper
header and row names?

No, no... I'm afraid, that oneself we don't understand. In my solution,
subform control does not contain form object, but query only.

Put in to form empty subform control named SbForm1 and then set its
SourceObject property. In VBA code would look it like this:

Me!SbForm1.SourceObject = "Query.YourCrossTabQuery1"
Me!SbForm1.SourceObject = "Query.YourCrossTabQuery2"

Or you can build Your crosstab query dynamically:
CurrentDb.QueryDefs("YourCrossTabQuery").SQL = "TRANSFORM ..."
Me!SbForm1.SourceObject = "Query.YourCrossTabQuery"

K.P.
www.access.vis.pl

 

[John Dumay]

Hi K.P.,

Now i understand!

Workd like a charm in the Form. Will now build the report.
many thanks for your assistance.

Regards,

John

 

[abraham ayala ortega]

hey

(...)

You can choose simple way or difficult...

The simple one is such:
1. Put subform on Your form
2. As SourceObject in Your subform set:
Query.YourCrossTabQuery
3. That's all ;-)

The difficult way... Hmmm...
This simple one will suffice You maybe? ;-)

K.P.
www.access.vis.pl