Hi Daniel
The SQL syntax for a crosstab query is:
TRANSFORM <value expression>
SELECT <row header fields>
PIVOT <column header field>
In the PIVOT clause, you can add an IN subclause to specify the column
headers:
PIVOT 'Week' & WeekNumber IN ('Week1', 'Week2', 'Week3')
Data will be omitted if the corresponding header is not in the list (for
example, Week4, Week5, etc would not appear) and the specified columns will
be present even if no data exists for them.
If you want your headings to be dynamic, then you can modify the SQL of your
saved query before exporting it.
First, create a string containing all your headings:
Dim dt as Date, i as Integer, sHeadings as String
dt = <calculated start date>
For i = 1 to 12
if dt > 1 then sHeadings = sHeadings & ", "
sHeadings = sHeadings & "'" & Format(dt, "dd/mm/yy") & "'"
dt = dt + 7
Next
You not have a string like this:
'04/05/09', '11/05/09', '18/05/09', ...
Of course, you must modify the Format function string to match the date
format of your column headers.
Now, modify your query to add some dummy headers - say A,B,C. This will
ensure that the the last thing in your query SQL is:
PIVOT something IN (something);
All we have to do is find the last "(" in the SQL string and replace
everything after it with our sHeadings string, plus ");".
Dim qd as QueryDef, sSQL as string
Set qd = CurrentDb.QueryDefs("query name")
sSQL = qd.SQL
i = InstrRev( sSQL, "(" )
sSQL = Left(sSQL, i) & sHeadings & ");"
qd.SQL = sSQL
qd.Close
Now do your export!
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi Daniel
Do those columns appear when you open the query view?
If not, then the problem is you need to define the column headings. You
can
do this from the query properties window, or by including an IN() section
in
the PIVOT clause of your SQL.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
I have got a application and export query to csv. It is running all
right except one problem. The query is a crosstab query and the
columns headers are the monday of 12 weeks since today. The problme
is that if there is no data for the last a couple of weeks, it does
not export the columns header at all. So the data looks like less than
12 weeks.
Is there a way to get around that?
Cheers
Daniel- Hide quoted text -
- Show quoted text -
Hi Graham,
Thank you for you reply, I can not do it in proerpties window because
the headings are dynamic based on the date the qurey is running. Can
you please explain more about the other option? What do you mean IN
section in PIVOT clause in SQL.
Cheers
Daniel
