dynamic crosstab

[ismail]

i have crosstab query it shows rowheading DATE and column headings like
station1 station2 station3....station32
like this

DATE st1 st2 ...... ..... st30

09/10/2005 9000 8000 12900
10/10/2005 7890 323 2300
......
.......
......


user should be able to select example. st1,st2 and st3 or sometime st3,st4
and st9 etc.
i mean it is dyanmic it will vary.
how can i achive this ...

any help greatly appreciated.

 

[Michel Walsh]

Hi,


Dynamically write the SQL query. The column heading (UI property) is
translated by an IN list after the PIVOT clause:



strSQL= TRANSFORM ... PIVOT expression " & "IN(" & yourListHere & ")"

where yourListHere is a string representation of a list, with the
appropriate delimiters if the constants are literal or dates.


Hoping it may help,
Vanderghast, Access MVP

 

[ismail]

can you me a sample please ...i find little complicated ???

 

[Michel Walsh]

Hi,




An example? If you start with:



strSQL= "TRANSFORM SUM(amount) as The Value
SELECT clientID
FROM myTable
GROUP BY clientID
PIVOT FORMAT( myDate, 'mmm') "



and decide to restrict the output to 'JAN' , 'FEB' and 'MAY':


strSQL=strSQL & " IN( 'JAN', 'FEB', 'MAY') "

myListBoxControl.RowSource = strSQL ' to display the result in a list box
control, as example




Hoping it may help,
Vanderghast, Access MVP