Creating an Annual Report

[Onihonker]

I need assistance on how to create a report which accomplishes the following tasks.

a)Sorts information by Month and provides a monthly total.
b)Sorts information by year and provides a yearly total.
c)Report must be self sustaining from one year to the next.

I have a table with the columns EntryDate and JobCost
I'd like the report to total monthly by the Entry date and then yearly the JobCost.

An example of what I am hoping the report will look like.

Annual Report (Report Name)
Month-- JobCostTotal

JANUARY TOTAL (totals entire month) 12500.00

FEBRUARY TOTAL (totals entire month) 12500.00

MARCH TOTAL (totals entire month) 12500.00

APRIL TOTAL (totals entire month) 12500.00

MAY TOTAL (totals entire month) 12500.00

JUNE TOTAL (totals entire month) 12500.00

JULY TOTAL (totals entire month) 12500.00

AUGUST TOTAL (totals entire month) 12500.00

SEPTEMBER TOTAL (totals entire month) 12500.00

OCTOBER TOTAL (totals entire month) 12500.00

NOVEMBER TOTAL (totals entire month) 12500.00

DECEMBER TOTAL (totals entire month) 12500.00

ANNUALTOTAL: (totals entire year for jobcost column)

Any help as to how to create this report will be helpful.

I send my thanks in advance.

 

[stopher]

You need to create a query. Something like:

SELECT Year([EntryDate]) AS ReportYear, Format([EntryDate],"mmm") AS CalMonth, Sum([JobCost]) AS TotalJobCost
FROM [COLOR="Red"]myTable[/COLOR]
GROUP BY Year([EntryDate]), Format([EntryDate],"mmm")

Once you have create the query you can create a report based on this query (change myTable to your table name). You can make ReportYear a parameter entry so that the user can call up a report for any desired year.

hth
Chris

 

[Onihonker]

Stopher: Thank you for helping me get this far.

The query creates a report now allows the user to sort by year and it places in the month and then gives a monthly total.

The problem I'm having now is it doesn't organize them by month as well. I don't know if I need to assign 1-12 values to each month to do this or if I can use the EntryDate again?

It kinda looks like this:
ReportYear -- CallMonth -- TotalJobCost (Column headers)
2008 Feb 231457.00
2008 May 231457.00
2008 Jan 231457.00
2008 April 231457.00
2008 Mar 231457.00
-------------------------------------
Total 231457.00

I'd like it to look like this:ReportYear -- CallMonth -- TotalJobCost (Column headers)
2008 Jan 231457.00
2008 Feb 231457.00
2008 Mar 231457.00
2008 April 231457.00
2008 May 231457.00
-------------------------------------
Total 231457.00

 

[Onihonker]

Solved

I found a solution to my sorting problem.

The link below helped me organize by year and month.
http://www.sqlhacks.com/index.php/Dates/GroupByMonth

Stopher: I found your solution combined with the above link provided the answer I needed. I thank you again for contributing.