Display Dynamic crosstab on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I dynamically generate a crosstab SQL from criteria selected on a form. I want to display the data from that query on a form. Any ideas???
TIA
Terry
 
Hi,



Dynamically build a query using the crosstab as data source, that
would alias each field name of the crosstab. The fields name of the crosstab
can be obtained, not necessary from the crosstab itself (that would make it
run unnecessary), but from a "SELECT DISTINCT pivotExpression FROM
theOriginalTable". Use the alias f1, f2, f3,... so you can bind the control
of the form/report to those fields, f1, f2, f3, ... at design time. Sure,
you can also bind the control source at the moment you open the report,
avoiding the dynamically built query, or even better, if the fields name are
always the same, place them in an IN clause, in the PIVOT:

.... PIVOT pivotExpression IN( 'f1', 'f2', 'f3' )


and the fields name f1, f2 and f3 are now recognized by the report (since
those fields would always be produced, ... but only those ! ).



Hoping it may help,
Vanderghast, Access MVP


Terry said:
I dynamically generate a crosstab SQL from criteria selected on a form. I
Click to expand...
want to display the data from that query on a form. Any ideas???
 
Back
Top