Need help with calculation

[rpw]

Hi,

My current report lists the top three sales results of each group. The
report has a hidden textbox in the detail area that "counts" which record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show the
difference between 1st place and second or third place. For example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?

 

[rpw]

Sorry to have bothered you all - I figured it out after I hit the "post"
button.

Just in case anyone else comes across this same problem, here's what I did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal], running sum
= No

 

[rpw]

Nope, that solution is wrong. I guess I do need some help.

Sorry to have bothered you all - I figured it out after I hit the "post"
button.

Just in case anyone else comes across this same problem, here's what I did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal], running sum
= No

Hi,

My current report lists the top three sales results of each group. The
report has a hidden textbox in the detail area that "counts" which record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show the
difference between 1st place and second or third place. For example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?

 

[Duane Hookom]

Add a text box to your group header:
Name: txtMaxGroup
Control Source: = Max([SalesAmount])
Then in your detail section, you can use a text box with a control source
of:
=[SalesAmount] - [txtMaxGroup]

--
Duane Hookom
MS Access MVP
--

Nope, that solution is wrong. I guess I do need some help.

Sorry to have bothered you all - I figured it out after I hit the "post"
button.

Just in case anyone else comes across this same problem, here's what I
did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal], running
sum
= No

Hi,

My current report lists the top three sales results of each group. The
report has a hidden textbox in the detail area that "counts" which
record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show
the
difference between 1st place and second or third place. For example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?

 

[rpw]

Hi Duane,

Thank you for this - it works perfectly. Sorry about the delay in getting
back to you. Thanks for all of your past help too - your solutions always
work.

Add a text box to your group header:
Name: txtMaxGroup
Control Source: = Max([SalesAmount])
Then in your detail section, you can use a text box with a control source
of:
=[SalesAmount] - [txtMaxGroup]

--
Duane Hookom
MS Access MVP
--

Nope, that solution is wrong. I guess I do need some help.

Sorry to have bothered you all - I figured it out after I hit the "post"
button.

Just in case anyone else comes across this same problem, here's what I
did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal], running
sum
= No

:

Hi,

My current report lists the top three sales results of each group. The
report has a hidden textbox in the detail area that "counts" which
record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show
the
difference between 1st place and second or third place. For example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?

 

[Duane Hookom]

I almost always test solutions before I reply. My Northwind.mdb is full of
tables and reports with poster's name like tblRPW or rptRPW.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thank you for this - it works perfectly. Sorry about the delay in getting
back to you. Thanks for all of your past help too - your solutions always
work.

Add a text box to your group header:
Name: txtMaxGroup
Control Source: = Max([SalesAmount])
Then in your detail section, you can use a text box with a control source
of:
=[SalesAmount] - [txtMaxGroup]

--
Duane Hookom
MS Access MVP
--

Nope, that solution is wrong. I guess I do need some help.

:

Sorry to have bothered you all - I figured it out after I hit the
"post"
button.

Just in case anyone else comes across this same problem, here's what I
did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal],
running
sum
= No

:

Hi,

My current report lists the top three sales results of each group.
The
report has a hidden textbox in the detail area that "counts" which
record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show
the
difference between 1st place and second or third place. For
example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?

 

[rpw]

That's a good idea - the few times that I've tried to help a poster, I'd
built a separate mdb to make sure I was getting things right.

I almost always test solutions before I reply. My Northwind.mdb is full of
tables and reports with poster's name like tblRPW or rptRPW.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thank you for this - it works perfectly. Sorry about the delay in getting
back to you. Thanks for all of your past help too - your solutions always
work.

Add a text box to your group header:
Name: txtMaxGroup
Control Source: = Max([SalesAmount])
Then in your detail section, you can use a text box with a control source
of:
=[SalesAmount] - [txtMaxGroup]

--
Duane Hookom
MS Access MVP
--

Nope, that solution is wrong. I guess I do need some help.

:

Sorry to have bothered you all - I figured it out after I hit the
"post"
button.

Just in case anyone else comes across this same problem, here's what I
did.

SalesAmount - running sum = No
txtSalesTotal - running sum = Over Group
txtSalesDiff - control source = [SalesAmount] - [txtSalesTotal],
running
sum
= No

:

Hi,

My current report lists the top three sales results of each group.
The
report has a hidden textbox in the detail area that "counts" which
record it
is (1, 2, 3) within each group (thanks Duane Hookom). For example:

Group One
Samuel $100,000 (1)
George $98,000 (2)
Jose $75,000 (3)
Group Two
Mary $50,000 (1)
Jane $45,000 (2)
Alice $35,000 (3)

Instead of showing the total sales for each record, I'd like to show
the
difference between 1st place and second or third place. For
example:

Group One
Samuel $0 (1)
George -$2,000 (2)
Jose -$25,000 (3)
Group Two
Mary $0 (1)
Jane -$5,000 (2)
Alice -$15,000 (3)

Can you help me with the calculation?