create a calculated field based upon grouping in a report

[BillC]

I have report based upon a query that has the following informaton:

Pt Acct CPT Code Description Status Tot Adj Pmt
37819 24120 xxxxx T $1,064.07
37819 24105 yyyyy T $1,064.07
37819 90784 zzzzz X $45.79
37819 73070 aaaaa X $40.55

I need to create a calculated field in the report that looks at the status
code and determines if it is "T" then pay the first "T" at 100%, the next
record for the pt acct with a status code of "T" would pay 50%. "X" is
always paid at 100%. There can also be a status code of "N" which does not
get paid. What is your suggestion to solve this?

 

[Duane Hookom]

Is there a sort order of the data? If not, then there is no "first" and
there is no reliable solution.

 

[BillC]

The sort order is defined within the sorting and grouping dialog box as
follows:
Pt acct (ascending)
In the next row I used the following switch statement
Switch([status]="T","a",[status]="S","b",[status]="X","c",[status]="A","d",[status]="N","e",[status]="C","f") (ascending order)
The third row:
Total Adjusted Payment (descending)

All of the data is in the order necessary for the calculation. Only the
first "T" (now "a")record needs to be multiplied by 100%. Each subsequent
"T" procedure is at 50%. All "S","X", and "A" are at 100% and "N" and "C" do
not receive any payment. I want to group by patient account number to
determine the payment for each patient.

Thanks for your assistance!

 

[Duane Hookom]

I would get rid of the Switch() function in favor of a small table with the
appropriate Status, sort order, and multiplier. You could then add a text
box in the detail section:
Name: txtPtCount
Control Source: =1
Running Sum: Over Group
Visible: No
You should then be able to tell if txtPtCount = 1. If so, use the multiplier
of 100% and if not use the multiplier of 50%.

--
Duane Hookom
MS Access MVP
--

The sort order is defined within the sorting and grouping dialog box as
follows:
Pt acct (ascending)
In the next row I used the following switch statement:
Switch([status]="T","a",[status]="S","b",[status]="X","c",[status]="A","d",[status]="N","e",[status]="C","f")
(ascending order)
The third row:
Total Adjusted Payment (descending)

All of the data is in the order necessary for the calculation. Only the
first "T" (now "a")record needs to be multiplied by 100%. Each subsequent
"T" procedure is at 50%. All "S","X", and "A" are at 100% and "N" and "C"
do
not receive any payment. I want to group by patient account number to
determine the payment for each patient.

Thanks for your assistance!

Is there a sort order of the data? If not, then there is no "first" and
there is no reliable solution.

 

[BillC]

I have done what you have said but I am having problems with the subtotals
for my calculated field call APC Payments. It is asking for a parameter
value for the group footer. Is this because I am using a control source =1
in my formula in the group footer. It is my understanding that the formula
in a calculated field must be repeated in the footer.
Thanks!

I would get rid of the Switch() function in favor of a small table with the
appropriate Status, sort order, and multiplier. You could then add a text
box in the detail section:
Name: txtPtCount
Control Source: =1
Running Sum: Over Group
Visible: No
You should then be able to tell if txtPtCount = 1. If so, use the multiplier
of 100% and if not use the multiplier of 50%.

--
Duane Hookom
MS Access MVP
--

The sort order is defined within the sorting and grouping dialog box as
follows:
Pt acct (ascending)
In the next row I used the following switch statement:
Switch([status]="T","a",[status]="S","b",[status]="X","c",[status]="A","d",[status]="N","e",[status]="C","f")
(ascending order)
The third row:
Total Adjusted Payment (descending)

All of the data is in the order necessary for the calculation. Only the
first "T" (now "a")record needs to be multiplied by 100%. Each subsequent
"T" procedure is at 50%. All "S","X", and "A" are at 100% and "N" and "C"
do
not receive any payment. I want to group by patient account number to
determine the payment for each patient.

Thanks for your assistance!

Is there a sort order of the data? If not, then there is no "first" and
there is no reliable solution.

--
Duane Hookom
MS Access MVP
--

I have report based upon a query that has the following informaton:

Pt Acct CPT Code Description Status Tot Adj Pmt
37819 24120 xxxxx T $1,064.07
37819 24105 yyyyy T $1,064.07
37819 90784 zzzzz X $45.79
37819 73070 aaaaa X $40.55

I need to create a calculated field in the report that looks at the
status
code and determines if it is "T" then pay the first "T" at 100%, the
next
record for the pt acct with a status code of "T" would pay 50%. "X" is
always paid at 100%. There can also be a status code of "N" which does
not
get paid. What is your suggestion to solve this?

 

[Duane Hookom]

I would use a text box (possibly hidden) in the detail section to calculate
the payments. Then set this text box running sum to Over Group. You can then
reference the text box control name in the group footer.

--
Duane Hookom
MS Access MVP

I have done what you have said but I am having problems with the subtotals
for my calculated field call APC Payments. It is asking for a parameter
value for the group footer. Is this because I am using a control source
=1
in my formula in the group footer. It is my understanding that the
formula
in a calculated field must be repeated in the footer.
Thanks!

I would get rid of the Switch() function in favor of a small table with
the
appropriate Status, sort order, and multiplier. You could then add a text
box in the detail section:
Name: txtPtCount
Control Source: =1
Running Sum: Over Group
Visible: No
You should then be able to tell if txtPtCount = 1. If so, use the
multiplier
of 100% and if not use the multiplier of 50%.

--
Duane Hookom
MS Access MVP
--

The sort order is defined within the sorting and grouping dialog box as
follows:
Pt acct (ascending)
In the next row I used the following switch statement:
Switch([status]="T","a",[status]="S","b",[status]="X","c",[status]="A","d",[status]="N","e",[status]="C","f")
(ascending order)
The third row:
Total Adjusted Payment (descending)

All of the data is in the order necessary for the calculation. Only
the
first "T" (now "a")record needs to be multiplied by 100%. Each
subsequent
"T" procedure is at 50%. All "S","X", and "A" are at 100% and "N" and
"C"
do
not receive any payment. I want to group by patient account number to
determine the payment for each patient.

Thanks for your assistance!

:

Is there a sort order of the data? If not, then there is no "first"
and
there is no reliable solution.

--
Duane Hookom
MS Access MVP
--

I have report based upon a query that has the following informaton:

Pt Acct CPT Code Description Status Tot Adj Pmt
37819 24120 xxxxx T $1,064.07
37819 24105 yyyyy T $1,064.07
37819 90784 zzzzz X $45.79
37819 73070 aaaaa X $40.55

I need to create a calculated field in the report that looks at the
status
code and determines if it is "T" then pay the first "T" at 100%, the
next
record for the pt acct with a status code of "T" would pay 50%. "X"
is
always paid at 100%. There can also be a status code of "N" which
does
not
get paid. What is your suggestion to solve this?