Totals by Month and Year

[ConfusedA]

Hi there,
I have 2 reports that are pretty much identical(in that they both contain job type, hours, price, year, month and week)

I was wondering if it was possible to find a way to be able to calculate price totals based on month and year. The queries calculate the prices of each job based on hours entered in a form and a set list of jobcosts in a table. If more information is needed to help understand what I am looking for, please let me know. If what I am trying to do is impossible, knowing that would be helpful as well.

Thanks for your time.
ConfusedA

 

[themurph2000]

It would take a lot to give the full explanation, but the quick answer is that you need a report with at least two different grouping levels. Both levels would be the Date field you are using; one broken down by month, one broken down by year. You can then have a group footer for each and do a Sum of the field of whatever it is you're trying to add. The Sorting and Grouping button in Report Design will help you with this.

 

[ConfusedA]

Thank you for your answer. I understand most of this other than the 'grouping footer' and the sum part. How do I create a 'grouping footer'? And How do I create a sum in it?

Thanks again,
A

 

[ConfusedA]

Hmm what I said wasn't clear at all. I have set up a Total Textbox, added it to the Page Footer, In Data I used Control Source: Price, and Running Sum: Over All, it only gives me the last piece of data though.

 

[themurph2000]

Hmm what I said wasn't clear at all. I have set up a Total Textbox, added it to the Page Footer, In Data I used Control Source: Price, and Running Sum: Over All, it only gives me the last piece of data though.

Right. What you're looking for is that same sum but at each change in the month or year, correct? To do that, you need two things. 1) An area following each month where they can be summed, and 2) the formula by which you sum the numbers.

1) The Sorting and Grouping button (looks like a big parenthesis, 2 smaller ones, and 4 lines) opens up a dialog box. You then choose the fields you want. There is a Grouping Properties section at the bottom of the dialog box. For each field you wish to group, you add a Group Footer by changing that option from No to Yes.

2) Choose the Text Box button from your toolbox and draw one in your group footer under where the numbers are. In the Control Source Property of the text box, you would create the formula

= sum ([fieldyouwanttoadd])

You can also search on the Sum function to assist you.

 

[ConfusedA]

Ok wow, that helped a lot thank you!

I have one last question about totals. I need to create one more from in which I can show the total prices for all jobs by month (which I have managed to create using your information)

BUT at the end I need to separate the prices in to two groups.
One group encompasses 2 'job' types, and the other encompasses the rest.
So currently I have a system that will give me a report like.

Month:
Job: TotalPrice TotalHours

etc.

at the bottom I'd like to have a totals like:
CountyJob: (Job1Price+Job2Price) CityJob: (AllJobsPrice - Countyjob)

Where the information in the brackets are the formulas i need to understand.
Is there a way that I can calculate this? What I have done is created a new field that labels all jobs as either 1 (cityjob) or 2 (countyjob), I just need to figure out how to be able to add up the prices solely for 1s and soley for 2s... so far this had eluded me.

Thanks again,
A