access report on a cross tab query

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

i have a cross tab query

which gives an output like below with following columns-------

ozip dzip week no1 week no2 week no3

columns week no1 , week no2 and so on could be dynamic-- it could also
be week no52, week no 51 etc

nowi i am trying to create a report based on the above query using
report wizard--- how should i create a report when column names keep
on changing based on what the user needs

pls advise thanks
 
subs said:
i have a cross tab query

which gives an output like below with following columns-------

ozip dzip week no1 week no2 week no3

columns week no1 , week no2 and so on could be dynamic-- it could also
be week no52, week no 51 etc

nowi i am trying to create a report based on the above query using
report wizard--- how should i create a report when column names keep
on changing based on what the user needs
Click to expand...


How do users specify what they need?

If they enter a start week and you always want a fixed
number of weeks after that week, then change the query to
pivot on the relative week number:
"W" & (WeekNum - [start week])
which will be columns like W0, W1, W2, ...
The fields will then always have the same names.

To get the column headings back to what you had before, use
text boxes (instead of labels) with expressions like:
="week no" & ([start week] + 0)
="week no" & ([start week] + 1)
="week no" & ([start week] + 2)
. . .
 
Marshall said:
subs said:
i have a cross tab query

which gives an output like below with following columns-------

ozip dzip week no1 week no2 week no3

columns week no1 , week no2 and so on could be dynamic-- it could also
be week no52, week no 51 etc

nowi i am trying to create a report based on the above query using
report wizard--- how should i create a report when column names keep
on changing based on what the user needs
Click to expand...


How do users specify what they need?

If they enter a start week and you always want a fixed
number of weeks after that week, then change the query to
pivot on the relative week number:
"W" & (WeekNum - [start week])
which will be columns like W0, W1, W2, ...
The fields will then always have the same names.

To get the column headings back to what you had before, use
text boxes (instead of labels) with expressions like:
="week no" & ([start week] + 0)
="week no" & ([start week] + 1)
="week no" & ([start week] + 2)
. . .
Click to expand...


subs posted follow up via private email:
Users enter the ship date range for example---1/1/2009
and 3/1/2009. so query returns all the week nos in
between those dates- like week no1 , week no 2 etc. They
can give one month, two months or three months any
duration lenght- i am using date part function for the
query. So how would i use it in a report?

If users enter a start date, then you can convert to
relative week numbers using an expression like:

"W" & (DatePart("ww", datefield) - DatePart("ww", [start
week]))
 
Back
Top