Subtract Groups

[davetedwards]

Alright I've got a report based of a query giving me information grouped by club and then by year and then month. I need to take the sum of units for a month and subtract the previous months total. I don't know how to get last months sum to subtract it. I've seen some things online about using if's but I don't see how that would work. I don't know if I need to create a new quarry where it renumbers all the months so that last month comes up as the same month?

Dave

[ItsMe]

In a new and seperate query you could create an alias field (along with other alias fields for report grouping, etc.) using the month function

CompareDate: Month([DateFieldName])+1

You would use this field to create a join to your original query.

If you can, do the calculations in the report.

Test it and see. If your new query does not support the report correctly you may have to go to a temp table. I think you should be able to get the join you need for your report though using this single alias in a new query.

[A S MANN]

You can Create a New Table Containg All Privious Month Units. Append Monthly data After End of Every Month.
Create New Query and in it Join This New table and Current Month Unit Ttotals
Take this new query as record Source of ur report

[davetedwards]

In a new and seperate query you could create an alias field (along with other alias fields for report grouping, etc.) using the month function

CompareDate: Month([DateFieldName])+1

You would use this field to create a join to your original query.

If you can, do the calculations in the report.

Test it and see. If your new query does not support the report correctly you may have to go to a temp table. I think you should be able to get the join you need for your report though using this single alias in a new query.

I wanted to do this. I set up a new query and had another column for last month which was equal to month +1, the month is just a number 1-12. The problem comes in the report, when I go to run it I'm prompted with whatever the table is called. The report is already grouping information by month. I there should be some easy way to say take the sum of units and subtract the sum of units from the month before.

[ItsMe]

Hmmmmm, you would need to build a second and separate query in the query builder. In this second query have a couple of collumns. One would have the date and the other would have your expression to add a number to the month of the date.

In you original query (that the report depends upon), add a column to it. This column would use the month function to get the actual and real number of the month for that given record.

With that, you can bring the second query you created into your first query. Join on the month number columns you created. Then create an alias for the date field of the second query. Creating an alias will help you to distinguish this field as an historical date value in your original query and within your report.