summing only part of a list of numbers on report? HELP PLEASE!!

[daddypost]

I will try to explain the best that I can. I have a 52 week sales report. There are budget numbers for the entire year already input into the report. As the weeks accumulate I add in the Actual numbers. When I do a total at the footer, the actual numbers of course add up year to date, but the budget numbers total the entire year. Is there a way to just total the budget numbers to match the ytd actual numbers. I will now try and show you visually what I'm talking about. This is the way its totaling now!

week Budget Actual % so basically
1 100,000 98,000 98% I would only
2 100,000 99,000 99% have 3 weeks
3 100,000 101,000 101% worth of actual
4 100,000 sales numbers.
5 100,000
6 .
7 .
. .
.
.
52 100,000
TOTAL 5,200,000 298,000 5.7%
(=sum[budget])=(sum[actual) =sum(actual)/sum(budget)
I'd like to have a YTD total line
YTD total 300,000 298,000 99.3%

Any ideas? I'm in a bind as my boss NEEDS to see this. THanks

 

[DoubleD]

You could write a calculated field in your report:

=IIF([Actual Sales] > 0, [Budget],0)

Then Sum the calculated field.

I am what I am based on the decisions I have made.

DoubleD

 

[dhookom]

DoubleD,
You can't ever Sum a control.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DoubleD]

Sorry to be unclear.
I meant that you would put a control in the footer that sums the calculation.

=Sum(IIF([Actual Sales]>0,[Budget],0))

I am what I am based on the decisions I have made.

DoubleD

 

[daddypost]

Dhookem,
would the sum(abs([actual]*[budget]) not actually give you total of actual*budget? abs=absoute value correct? so if there is a number in actual the budget number would be huge? maybe I'm reading that wrong.
I just used Dsum and its working fine for right now. My problem will be if for some reason a rep has 0 sales for a week and actually has numbers budgeted for that week. Guess I'll cross that bridge when I come to it or I can get an idea from here to work. And Dhookem, I'm definitely not suggesting yours doesn't work, I'm just confused on what the ABS([actual])* budget number will actually give you.

 

[DoubleD]

daddypost,
Would IsNull work instead of > 0?

=IIF(IsNull([Actual Sales]), 0, [Budget])

I am what I am based on the decisions I have made.

DoubleD

 

[dhookom]

daddypost,
My suggestion was
=Sum(Abs([Actual]<>0) * [Budget])
not
sum(abs([actual]*[budget])
Do you see the difference?

DSum() is a horrible resource hog and could be inaccurate. If your [Actual] for a week might be 0 and yet you want to include the week in the total, you could try:
=Sum((IsNull([Actual])=False) * [Budget])
This would assume that you want to include all the weeks where the Actual value is not null. If that doesn't work, you need to define for us how to determine which [Budget] weeks to sum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]