Trouble with sorting monthly

[Alliana Gray]

I have a table that has many fields, the ones of concern are the YTD field which lists a month and year, a total amount of waste in pounds, and a location.

I'm sorting the records by several locations. Let's say I have records for FL, CA, and VA. They each have records for total amount of waste and several different months.

I'm creating a summary report for this and I want it to display everything by month. However when I run the report, it'll display June 2011 several times, displaying the individual records for each different location. Is there any way I can get it to add the waste from each location and display it in one month?

If that's a little too vague, I'll try to clarify it. Let's say I have a record each for FL, CA and VA. They have unique amount of waste but all have June 2011 as their YTD. On the report, I don't want it to display FL, CA, and VA individually, I want it to add the waste together and display one record for June 2011.

Help would be greatly appreciated!

[NTC]

Well, one uses a Select Query type to select records from a table.

One uses an Aggregate Query type to do sums. As with any query - one can make a new query using an existing query - so that your sum is just of the records you select in the first query.

Or you can sum in the group footer of a record or footer of a form.

Depending how/where you want to display your sum would determine which of these tools you use.

Hope it helps.

[Alliana Gray]

How do you do an aggregate query?

[rpeare]

IN your query design view you should see a sigma button (the greek letter that looks like an E). This will add a row to your query that says TOTALS. if you SUM the waste amount and leave the other two fields as is it should total by location and month.

[Alliana Gray]

i'm trying to put this in a report, and not in a query.

[rpeare]

So base the report on a query instead (or at the very least a SQL statement). Otherwise you would have to do it all in report footers. If you don't want to create a query add a footer section for your state and month and in the month footer put your month and the sum of the waste weight. and make sure your detail section is 0 height.

[Alliana Gray]

I have based the report off a query however everything I try either returns the months May and July, leaving out all the June records, or it will display May, July and June twice.

[rpeare]

Can you post a sample database with some fake data. It would be easier to diagnose your problem.

[Alliana Gray]

I have solved the problem with the reports. It seems that even though the record says June 2011, Access had it stored as 6/1/2011 and 6/25/2011. Because there were different days in June in the table, Access was showing June several times because they were all different days. I have since gone back and correctly set the records all as June 2011 and the report now works fine. Thanks for everyone's help!