report on a cross tab query

[subs]

subs
View profile
More options Feb 7, 2:38 pm
Newsgroups: microsoft.public.access.queries
From: subs <[email protected]>
Date: Sun, 7 Feb 2010 11:38:53 -0800 (PST)
Local: Sun, Feb 7 2010 2:38 pm
Subject: access report on a cross tab query
Reply | Reply to author | Forward | Print | Individual message | Show
original | Remove | Report this message | Find messages by this author
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

Reply Reply to author Forward



You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.


Marshall Barton
View profile
More options Feb 7, 7:21 pm
Newsgroups: microsoft.public.access.queries
From: Marshall Barton <[email protected]>
Date: Sun, 07 Feb 2010 18:21:35 -0600
Local: Sun, Feb 7 2010 7:21 pm
Subject: Re: access report on a cross tab query
Reply | Reply to author | Forward | Print | Individual message | Show
original | Report this message | Find messages by this author

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

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)
. . .
--
Marsh


Users will specify a date range-----1/1/2009 and <2/1/2009 and week
nos will be based on the date range. so how to create a report where
week nos can be variable depending on the users input thanks

 

[Duane Hookom]

The column names will not change if you followed Marsh's instructions. He
also stated how to create column headings. What did you try and what isn't
working?

There is another explanation of this solution at
http://www.tek-tips.com/faqs.cfm?fid=5466.

Duane Hookom
MS Access MVP

subs
View profile
More options Feb 7, 2:38 pm
Newsgroups: microsoft.public.access.queries
From: subs <[email protected]>
Date: Sun, 7 Feb 2010 11:38:53 -0800 (PST)
Local: Sun, Feb 7 2010 2:38 pm
Subject: access report on a cross tab query
Reply | Reply to author | Forward | Print | Individual message | Show
original | Remove | Report this message | Find messages by this author
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

Reply Reply to author Forward



You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.


Marshall Barton
View profile
More options Feb 7, 7:21 pm
Newsgroups: microsoft.public.access.queries
From: Marshall Barton <[email protected]>
Date: Sun, 07 Feb 2010 18:21:35 -0600
Local: Sun, Feb 7 2010 7:21 pm
Subject: Re: access report on a cross tab query
Reply | Reply to author | Forward | Print | Individual message | Show
original | Report this message | Find messages by this author

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

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)
. . .
--
Marsh


Users will specify a date range-----1/1/2009 and <2/1/2009 and week
nos will be based on the date range. so how to create a report where
week nos can be variable depending on the users input thanks