Monthly/Yearly Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a report to show a monthly total of received
applications per each application type. Now this is what i would like it to
look like:

Application Type JAN FEB MAR APR ... DEC TOTAL
A 20 20 20 20 20 100
B 20 50 10 10 10 100
..
..
..


Now i've created a report where i include the fields of Application Type and
Received Date. In the Detail part of the report I have an invisible textbox
per each month that is unbound where sum part is Over Group and the Control
Source is:

=IIf((([Date Rec]>=DateSerial(Year(Date()),2,1)) And ([Date
Rec]<DateSerial(Year(Date()),3,1))),1,0) * The 2 and 3 change to the month
of reporting.

So i'm using these to 'count' each application per month, but it doesnt seem
to be working correctly as the numbers i get are not what matches the hand
counting.

In the Application Type Report Footer i have a layout such as

[Application type] [jancount] [febcount] [marcount] ... [deccount]
[=jancount+..+deccount]

Any help would be appreciated.

Thanks,
Tim
 
Have you considered creating a crosstab query that has your application type
as the Row Heading, Format([Date Rec],"mmm") as the Column Heading, and
Count([ApplicationType]) as the Value?
 
I have never done one of these, would you happen to have an example i could
see or some useful resources to read up on this information. I'll do a
search and see what i can find on the net.

Thanks for the direction,
Tim

Duane Hookom said:
Have you considered creating a crosstab query that has your application type
as the Row Heading, Format([Date Rec],"mmm") as the Column Heading, and
Count([ApplicationType]) as the Value?

--
Duane Hookom
MS Access MVP
--

Tim said:
I'm trying to create a report to show a monthly total of received
applications per each application type. Now this is what i would like it
to
look like:

Application Type JAN FEB MAR APR ... DEC TOTAL
A 20 20 20 20 20 100
B 20 50 10 10 10 100
.
.
.


Now i've created a report where i include the fields of Application Type
and
Received Date. In the Detail part of the report I have an invisible
textbox
per each month that is unbound where sum part is Over Group and the
Control
Source is:

=IIf((([Date Rec]>=DateSerial(Year(Date()),2,1)) And ([Date
Rec]<DateSerial(Year(Date()),3,1))),1,0) * The 2 and 3 change to the
month
of reporting.

So i'm using these to 'count' each application per month, but it doesnt
seem
to be working correctly as the numbers i get are not what matches the hand
counting.

In the Application Type Report Footer i have a layout such as

[Application type] [jancount] [febcount] [marcount] ... [deccount]
[=jancount+..+deccount]

Any help would be appreciated.

Thanks,
Tim
Click to expand...
Click to expand...
 
I think there is at least one crosstab in Northwinds. There is a crosstab
wizard when creating a new query.

--
Duane Hookom
MS Access MVP
--

Tim said:
I have never done one of these, would you happen to have an example i could
see or some useful resources to read up on this information. I'll do a
search and see what i can find on the net.

Thanks for the direction,
Tim

Duane Hookom said:
Have you considered creating a crosstab query that has your application
type
as the Row Heading, Format([Date Rec],"mmm") as the Column Heading, and
Count([ApplicationType]) as the Value?

--
Duane Hookom
MS Access MVP
--

Tim said:
I'm trying to create a report to show a monthly total of received
applications per each application type. Now this is what i would like
it
to
look like:

Application Type JAN FEB MAR APR ... DEC TOTAL
A 20 20 20 20 20 100
B 20 50 10 10 10 100
.
.
.


Now i've created a report where i include the fields of Application
Type
and
Received Date. In the Detail part of the report I have an invisible
textbox
per each month that is unbound where sum part is Over Group and the
Control
Source is:

=IIf((([Date Rec]>=DateSerial(Year(Date()),2,1)) And ([Date
Rec]<DateSerial(Year(Date()),3,1))),1,0) * The 2 and 3 change to the
month
of reporting.

So i'm using these to 'count' each application per month, but it doesnt
seem
to be working correctly as the numbers i get are not what matches the
hand
counting.

In the Application Type Report Footer i have a layout such as

[Application type] [jancount] [febcount] [marcount] ... [deccount]
[=jancount+..+deccount]

Any help would be appreciated.

Thanks,
Tim
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane, I'll take a look at it and see what i can do. I appreciate the
help and i'll let you know if i run into any type of trouble.

Tim

Duane Hookom said:
I think there is at least one crosstab in Northwinds. There is a crosstab
wizard when creating a new query.

--
Duane Hookom
MS Access MVP
--

Tim said:
I have never done one of these, would you happen to have an example i could
see or some useful resources to read up on this information. I'll do a
search and see what i can find on the net.

Thanks for the direction,
Tim

Duane Hookom said:
Have you considered creating a crosstab query that has your application
type
as the Row Heading, Format([Date Rec],"mmm") as the Column Heading, and
Count([ApplicationType]) as the Value?

--
Duane Hookom
MS Access MVP
--

I'm trying to create a report to show a monthly total of received
applications per each application type. Now this is what i would like
it
to
look like:

Application Type JAN FEB MAR APR ... DEC TOTAL
A 20 20 20 20 20 100
B 20 50 10 10 10 100
.
.
.


Now i've created a report where i include the fields of Application
Type
and
Received Date. In the Detail part of the report I have an invisible
textbox
per each month that is unbound where sum part is Over Group and the
Control
Source is:

=IIf((([Date Rec]>=DateSerial(Year(Date()),2,1)) And ([Date
Rec]<DateSerial(Year(Date()),3,1))),1,0) * The 2 and 3 change to the
month
of reporting.

So i'm using these to 'count' each application per month, but it doesnt
seem
to be working correctly as the numbers i get are not what matches the
hand
counting.

In the Application Type Report Footer i have a layout such as

[Application type] [jancount] [febcount] [marcount] ... [deccount]
[=jancount+..+deccount]

Any help would be appreciated.

Thanks,
Tim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top