I need to total across a line in an Access 2007 report. My 'Totals' selection is not live but I don't know why. Anyone know how to total a line?

The columns are values by month: Jan, Feb, Mar, Apr, etc. I tried using a control with Sum([Jan] + [Feb] + [Mar] + [Apr]) but it doesn't work.

Can you explain a little bit more regarding your data. If you did a Crosstab Query, then you could do a Total of the line, BUT it all has to do with your Table or Query.

RAZMaddaz

Yes, I'm working with a crosstab query that sorts out payments by month. The columns are month names and I want to total the values.
Here is crosstab:

TRANSFORM Sum([Capital Expense].[Trans Amount]) AS [SumOfTrans Amount]
SELECT [Capital Expense].[Project ID], [Capital Expense].[Project Description]
FROM [Capital Expense], tblCapExpByMonth
WHERE ((([Capital Expense].[Trans Date])>=[tblCapExpByMonth]![StartDate] And ([Capital Expense].[Trans Date])<=[tblCapExpByMonth]![EndDate]))
GROUP BY [Capital Expense].[Project ID], [Capital Expense].[Project Description]
ORDER BY [Capital Expense].[Project ID], IIf(Month([Trans Month])=1,"Jan",IIf(Month([Trans Month])=2,"Feb",IIf(Month([Trans Month])=3,"Mar",IIf(Month([Trans Month])=4,"Apr",IIf(Month([Trans Month])=5,"May",IIf(Month([Trans Month])=6,"Jun",IIf(Month([Trans Month])=7,"Jul",IIf(Month([Trans Month])=8,"Aug",IIf(Month([Trans Month])=9,"Sep",IIf(Month([Trans Month])=10,"Oct",IIf(Month([Trans Month])=11,"Nov",IIf(Month([Trans Month])=12,"Dec",Null))))))))))))
PIVOT IIf(Month([Trans Month])=1,"Jan",IIf(Month([Trans Month])=2,"Feb",IIf(Month([Trans Month])=3,"Mar",IIf(Month([Trans Month])=4,"Apr",IIf(Month([Trans Month])=5,"May",IIf(Month([Trans Month])=6,"Jun",IIf(Month([Trans Month])=7,"Jul",IIf(Month([Trans Month])=8,"Aug",IIf(Month([Trans Month])=9,"Sep",IIf(Month([Trans Month])=10,"Oct",IIf(Month([Trans Month])=11,"Nov",IIf(Month([Trans Month])=12,"Dec",Null))))))))))));

I'm trying to do this report as a favor to someone so I just had this tossed at me and have no choice about how things are structured. I wrote the query and am attempting the report.
Any help is appreciated.

You should not have to create a bunch of IF functions to change the Trans Month Field from a Number to the Month, you could just use the Month function. If you use the Crosstab wizard it will all ask you at the end if you want a Total too. After the crosstab wizard is done is when you would change the formatting of the Month Field. If the Field is a Date Type, the wizard will ask you how you want to group the data, such as by Week, Month, Year. Understand?

If you want, I could take a look at your database and create a Crosstab for you, BUT, make a copy, delete any private data, zip the copied database and upload the zipped file.

RAZMaddaz

Wait, I just realized that I have an even bigger problem. If there is no data for a particular month, it won't deliver that month as a column of data. Therefore, the report will fail. Need to take another look at this. I've approached this task from 4 or 5 different directions already and just keep hitting brick walls. Thanks for looking though, RAZ.

Here is an example of a Crosstab by Month.

Correct and that is when you need to click in the Column Heading of the Query, view the Property Sheet and then enter in the Column Headings the Dates you want to see. This will then show the Dates, even when there is no data for them.

Edit: You will see this in the database I uploaded too.

Thanks for the query RAZ. I used the format month from it and was happy to find out that I can set my columns on the properties. I still cannot get the report to total year-to-date across for me.
I tried the query wizard but it never did prompt me for totals like you indicated. The query is a little more complex than just one table so the wizard only captures part of what I need. I rarely ever use wizards because they only deal with simple queries.

Project ID Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun YTD
MIDET10010008 100 200 100 300 -100
MIDET10210221 50 100
MIDET10600012 300 500 400 100 100 200 300 400 100 600
MIDET10600012 300
MIDET11010001 100 100 200 100 100 100 400 100 200
MIDET11010003 300 400 100 100 200 300 400 500 100
MIDET11010019 100

This is run by fiscal year to track costs year-to-date. If I can get the darn thing to add the months across I can give this back and go back to my real job. (lol) I can't believe this is causing me so much grief. I know it's has to be something really simple.

If you look at the Crosstab in design, what Field do you have in the Sum Value? If if a new Column, you enter that Field like Total of XXX: TheValueFieldName, make the Total a Sum and Crosstab Row "Row Heading", does that work?

I don't believe how patient you are being, RAZ. Thank you so much. I just don't know where to go from here.

Here is a copy of the report and query. (I hope it attaches)

Look at the new Query. In the new Query, look what I entered for the Column Heading, as oppose to all the IF functions you had before. Also look at the second Query, it got rid of all the other Tables that aren't needed too. BTW, I noticed for the Criteria you entered Between 7/1/2011 and 6/1/2012, well that isn't retrieving data for a Year time period. you might want to change it to Between 7/1/2011 and 6/30/2012?

RAZ

You are a great and wonderful man, RAZ. It was that total column that I wasn't grasping from your posts. I did change my query to your Month function but sent you the wrong one. Unfortunately, the other tables will be needed. To make this dynamic, the user will be prompted for the fiscal year. Since you can't use a prompt in a crosstab query (from what I found), I had to enable another way for the dates to be passed. I appreciate your efforts to educate us lesser beings. We learn what we need to get the job done.

When you create a Crosstab Parameter, you need to enter the Parameter in the Query Parameters area, otherwise the Query will not work like you mentioned. Look at "Qry....3" and click on the the Parameters icon and you will see that I had to enter "[Enter the Start Date:]" and select a Data Type. If you plan on entering a Time Period longer than one year then you will need to change the Column Headings and the Format of the Column Heading in the Query too.

RAZ

Thanks again, RAZ. Duh on the parameters, just spaced right by that. Unfortunately still need other tables because of the formatting of the Trans Month field (report based on). Can't do a ,+ and >= on a text field. And, of course, been asked for more fields and hoops to jump through. Darn thing is working now though! Thanks again.

Awesome!!! Glad I could help!!!

RAZMaddaz