Report Layout for Vertical and Horizontal

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dmackcwby - 12 Mar 2008 18:30 GMT

I am setting up a report that pulls information from two differant tables.
The data has a list of reasons that people have called our flor support
personal. I am trying to to show the data grouped by reason type and then a
total number recieved per month. The layaout should look like this:

Reason Jan Feb Mar Apr May
RT ## ## ## ## ##
RT ## ## ## ## ##
RT ## ## ## ## ##
RT ## ## ## ## ##

I've tried pulling the report from the tables and also from a query. What
do I need to do to get the information to display in this fashion?

Thanks in advance for your help.

Reply to this Message

Stockwell43 - 12 Mar 2008 18:41 GMT

In your report go to the Group/Sort button and in the first drop down select
your Reason field and at the bottom select group header "Yes" and group
footer "Yes". on the report, place the reason field in the group header and
the months in the detailed area. Now highlight all the month fields copy and
paste them in the group footer. On each of those fields try this in the
Control Source: =Sum([Jan]) and in the next month place =Sum([Feb]) and so
on. when you open the report you should see the reason in the top left
corner, how many of that reason was inputted with a number for each month and
in the bottom of that section you should see your totals. Place a line in the
details section to separate each reason.

> I am setting up a report that pulls information from two differant tables.
> The data has a list of reasons that people have called our flor support
[quoted text clipped - 11 lines]
>
> Thanks in advance for your help.

Reply to this Message

Dmackcwby - 12 Mar 2008 19:37 GMT

I guess I should have given you some more information. The two tables are as
follows(only showing fields that are needed for this report):

FsLog (LogID,Date,ReasonId)
FsReasons (ReasonID,Reason)

The two tables are linked through the ReasonID. I list the reasons on the
left and then I get the count from the LogID. I need the count grouped by
month by going across as mentioned in my previous note.

I hope that makes more sense.

> In your report go to the Group/Sort button and in the first drop down select
> your Reason field and at the bottom select group header "Yes" and group
[quoted text clipped - 22 lines]
> >
> > Thanks in advance for your help.

Reply to this Message

John Spencer - 13 Mar 2008 13:08 GMT

Sounds as if you need a Crosstab query as the source for your report. The
SQL for such a query would look like

TRANSFORM Count(L.ReasonID) as TheCount
SELECT R.Reason
, Year(L.Date) as TheYear
, Count(L.ReasonID) as YearCount
FROM FsLog as L INNER JOIN FsReasons as R
ON L.ReasonID = R.ReasonID
GROUP BY R.Reason, Year(L.Date)
PIVOT Format(L.Date,"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

You should be able to paste that as the source for your report.
If you need to build the crosstab query using Design View (query grid) you
can post back for instructions on how to do it.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I guess I should have given you some more information. The two tables are
>as
[quoted text clipped - 44 lines]
>> >
>> > Thanks in advance for your help.

Reply to this Message