Arranging a Report to look like a table

[Guest]

The situation is that I need to create a report that shows montly values for
9 different locations. I ran the query wizard to calculate the value for each
of the 9 locations. I have to show how much money each locations has
collected for every month of the year. The preferred way of this being shown
is as follows:

Location Jan # Feb # Mar#......... 6 months total #

1 $300 15 x x x x x x
2
3

So the top half should look something like that and the bottom half should
look the same as well except the months would be different. And at the end
instead of the 6months total it would the 12 months total. Also after each
month there is a # sign because it needs to be shown how many opportunities
that location has had in collecting money. So for example: for location 1
they collected $300 in 15 in January, in february it would be different. The
query already took care of since I had it count the # of records for each
month for each location. The report is landscape view so top half should have
6 months and bottom should also have 6 months and the location names need to
be displayed both at the top and bottom columns. I just need a way to punch
out what the query gave me in this format. I used the wizard but it was
useless and I don't know how to get the report to display the query that way
using design view. Thanks in advance if anyone can help and thanks for the
answers to my last question with the Shorts Database. This is actually for
that same databse but of course I put it in a different context.

 

[SA]

Van:

You need to create a crosstab query for your report's underlying record
source. Set the crosstab query's columns property headings to be something
like 1, 2, 3, 4 etc. For the columns field of the report use the DatePart
function to return only the month value and group on those dates. Last in
your report for the fields that you have for the months, use the Choose
function as the control source to convert to a text value as in:

=Choose([MyHeadingField],"Jan","Feb","Mar"......)

Hope this helps

 

[Guest]

Thanks for all your help. I did the exact same thing when playing around with
it and got the sum, count, everything. The only problem now though is that
the report needs to show the months of sept through december even when there
is no data on those months yet. The latest data is august so the rest of the
months are non existant yet. Obviously the crosstab only used the data there.
Is there a way for the rest of the months to also be shown on this report
even if it would return a $0.00 value for each location and a count of 0? And
as the data comes in for those months it would automatically update the
crosstab query thereby updating the report? Or does a crosstab need to be run
everytime new data comes in? Thanks for all your help, again its been very
helpful.

Van:

You need to create a crosstab query for your report's underlying record
source. Set the crosstab query's columns property headings to be something
like 1, 2, 3, 4 etc. For the columns field of the report use the DatePart
function to return only the month value and group on those dates. Last in
your report for the fields that you have for the months, use the Choose
function as the control source to convert to a text value as in:

=Choose([MyHeadingField],"Jan","Feb","Mar"......)

Hope this helps
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

The situation is that I need to create a report that shows montly values
for
9 different locations. I ran the query wizard to calculate the value for
each
of the 9 locations. I have to show how much money each locations has
collected for every month of the year. The preferred way of this being
shown
is as follows:

Location Jan # Feb # Mar#......... 6 months total #

1 $300 15 x x x x x x
2
3

So the top half should look something like that and the bottom half should
look the same as well except the months would be different. And at the end
instead of the 6months total it would the 12 months total. Also after each
month there is a # sign because it needs to be shown how many
opportunities
that location has had in collecting money. So for example: for location 1
they collected $300 in 15 in January, in february it would be different.
The
query already took care of since I had it count the # of records for each
month for each location. The report is landscape view so top half should
have
6 months and bottom should also have 6 months and the location names need
to
be displayed both at the top and bottom columns. I just need a way to
punch
out what the query gave me in this format. I used the wizard but it was
useless and I don't know how to get the report to display the query that
way
using design view. Thanks in advance if anyone can help and thanks for the
answers to my last question with the Shorts Database. This is actually for
that same databse but of course I put it in a different context.