Crosstab query report NOT summing over group

[ineedahelp]

Hello, I have created a crosstab query to summarize my data by month across RESOURCETYPE. The final column in my report needs to display sum for each RESOURCETYPE. I need to sum over the group and then over the whole report. I think my trouble lies in my YTDTOTAL. My main trouble is that my report sums over group for the first group but not the next group. any thoughts? Thanks. Here is the code for my crosstab query:

TRANSFORM Sum(IIf([hoursworked]<>0,([TaskPerformed]![HoursWorked]*[ProjectMember]![Rate]),[TaskPerformed]![FeeAmount])) AS TotFee
SELECT ProjectMember.VendorName, IIf([ResourceType]="External","Vendors","Internal") AS GroupHead, TaskPerformed.Action, Sum(IIf([hoursworked]<>0,([TaskPerformed]![HoursWorked]*[ProjectMember]![Rate]),[TaskPerformed]![FeeAmount])) AS YTDTotal
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
WHERE (((TaskPerformed.DatePerformed) Between #1/1/2009# And #12/31/2009#))
GROUP BY ProjectMember.VendorName, IIf([ResourceType]="External","Vendors","Internal"), TaskPerformed.Action
PIVOT Format([DatePerformed],"mm-yyyy");

I have put in my report... =sum(ytdTotal) as the last column in my detail section and also in the group section. I sorry but didn't know how to show you my report.

 

[dhookom]

=sum(ytdTotal) as the last column in my detail section

Summing anything in the detail section should display the sum for the entire report's record source. If you want group sums, you have to put the Sum() in a group header or footer section.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Im sorry I wasn't totally clear. I created an expression in my crosstab query called YTDTotal. It generates the same value as the VALUE expression in the query. I am displaying successfully each month of data. At the end of the detail row i have added a text box with =sum(YTDTotal) as the control source. This works great. in the group footer I have another text box with =sum(YTDTotal) as the control source and have it set to SUM OVER GROUP. For my first group it sums the group perfectly. The second group isnt a sum of that new group...it is the sum of ALL groups as if I had used SUM OVER ALL. My only problem is that this group footer is really summing OVER ALL even though set to OVER GROUP. I feel that it has something to do with my query and using YTDTotal which sums the months. I hope this extra info helps. thanks.

 

[dhookom]

When I create a report with a numeric field like YTDTotal, and place a text box in the detail section with a control source of:
=Sum(YTDTotal)
I expect it display the total of YTDTotal for the entire record source. The text box does not display any different value in the report. If my report total for the YTDTotal field is 1,063.47 then it is 1,063.47 in every detail record.

If I put the same control source in a group Header or Footer section, I see a group total for YTDTotal. Again, this is what I expect.

I am not using any running sums or other settings.

I'm not sure how yours is any different. What am I missing regarding your setup?


Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Maybe I should attack this issue in a different manner. i would like my report to display expense data on a monthly basis, ie 12 columns. I would like my 13th column to sum all these months. I would then like to take this YTD value and sum it over the whole group and finally over the whole report. My problem is how to SUM each month's value. in my crosstab query i have**** TotFee: Sum(IIf([hoursworked]<>0,([TaskPerformed]![HoursWorked]*[ProjectMember]![Rate]),[TaskPerformed]![FeeAmount]))***** as an EXPRESSION/VALUE and another expression ****** YTDTotal: Sum(IIf([hoursworked]<>0,([TaskPerformed]![HoursWorked]*[ProjectMember]![Rate]),[TaskPerformed]![FeeAmount]))*****as EXPRESSION/ROWHEADING. I only created the YTDTotal expression because I couldn't figure out how to sum up all 12 months for each record. I hope this is more clear. thanks again

 

[dhookom]

Doesn't this column from your previous SQL (row heading) display the total for all months?

Sum(IIf([hoursworked]<>0,([TaskPerformed]![HoursWorked]*[ProjectMember]![Rate]),[TaskPerformed]![FeeAmount])) AS YTDTotal

If it doesn't display the total for all monthly columns for each row in your crosstab, what does it display?


Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Yes it does! And, it works in my report as a way for me to get YTD totals (all available months added up) for each record. This is not my problem. My problem is that when I use YTDTotal in the GROUP FOOTER my report "sums over group" for only the first group. For the rest it is acting as if I choses "sum over all". It really isn't working for the first group either, it only looks that way because it is the first...it is actually a running sum of previous records.

 

[ineedahelp]

Here is a sample of my current report. Please note the first group VENDORS gets summed correctly. The second group INTERNAL get a running sum over all, not OVER GROUP. I hope this helps...thanks.

Vendors Jan Feb Mar Apr YTD
Abc Vendor 0 80 0 0 80
Xyz Vendor 40 0 225 0 265
First 0 0 2500 0 2500
Other 100 225 555 0 880
Next 0 400 0 0 400
Last 30 0 0 0 30
Total Vendor 4155


Internal
employee 0 0 6500 2500 9000
Other 720 1320 150 0 2190
Last 1030 490 325 0 1845
Total Internal 17190 (over group)
Total Vendor & Internal 17190 (Over all)

 

[dhookom]

I am not using any running sums or other settings.

It looks like you simply have the Running Sum property set. This would cause the results you are getting.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Oops. I should have prviewed first

I am not using any running sums or other settings.

It looks like you simply have the Running Sum property set. This would cause the results you are getting.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

You nailed it! I guess I was suckered into the running sums property as it sounded like what I wanted...running sum over group. Although it works...I am still a little baffled by why "running sum over group" didn't "sum the group" continuously. Oh well...thank you for sticking it out with me!