Need report to show only longest of 2 periods

[AccessNeophyte]

Greetings,

I'll try to explain this as best I can; please lt me know if you need
more info.

My report needs to show how many Clients were served in a given month,
based on which Contract they are under. Pretty simple so far. The
query takes care of that, and I have the report totaling by Contract
in the Group Footer {Text box with the ControlSource
"=[ContractCount]"}, and the total of all in the Report Footer {Text
box with the ControlSource "=Count(*)"}.

My test month (May) showed
30 Clients Served under ContractA
15 Clients Served under ContractB.
45 total

The complication is this: If a client is admitted under ContractA, and
then later "moved" to ContractB (or vice-versa), they are only counted
once, under the contract that they were under for the longest period
during the month.

This is my example:
Part 1:
Client X was admitted on 5/1/07 under ContractA, and was "moved" to
ContractB on 5/24/07. I've handled this in Access by giving the
client a "pretend" DischargeDate of 5/24/07, with a DischargeReason of
"Contract Change" and "admitting" him again under ContractB on
5/24/07.

So for May, he was under ContractA for 23 days, and then was moved to
ContractB for the last 8 days. Client is counted once, under longest
contract, so ContractA stays at 30 Clients Served, and under
ContractB, 15 Clients Served becomes 14

This means we have
30 Clients Served under ContractA
14 Clients Served under ContractB.
44 total

Part 2:
Client Y was admitted on 5/1/07 under ContractA, and was "moved" to
ContractB on 5/3/07. I've handled this in Access by giving the client
a "pretend" DischargeDate of 5/3/07, with a DischargeReason of
"Contract Change" and "admitting" him again under ContractB on
5/3/07.

So for May, he was under ContractA for 2 days, and then was moved to
ContractB for the last 29 days. Client is counted once, under longest
contract, so ContractA, 30 becomes 29, and ContractB stays at 14.

This means we have
29 Clients Served under ContractA
14 Clients Served under ContractB.
43 total

How do I get this final result in my report in Access?

I know I can have the text box on the report say {visible only if
DischargeReason is not "Contact Change"} but that might make the
longer instance invisible, simply because the longer instance came
first.

Any ideas? I'm lost.

Thanks,
Liz

 

[AccessNeophyte]

Greetings,

I'll try to explain this as best I can; please lt me know if you need
more info.

My report needs to show how many Clients were served in a given month,
based on which Contract they are under. Pretty simple so far. The
query takes care of that, and I have the report totaling by Contract
in the Group Footer {Text box with the ControlSource
"=[ContractCount]"}, and the total of all in the Report Footer {Text
box with the ControlSource "=Count(*)"}.

My test month (May) showed
30 Clients Served under ContractA
15 Clients Served under ContractB.
45 total

The complication is this: If a client is admitted under ContractA, and
then later "moved" to ContractB (or vice-versa), they are only counted
once, under the contract that they were under for the longest period
during the month.

This is my example:
Part 1:
Client X was admitted on 5/1/07 under ContractA, and was "moved" to
ContractB on 5/24/07. I've handled this in Access by giving the
client a "pretend" DischargeDate of 5/24/07, with a DischargeReason of
"Contract Change" and "admitting" him again under ContractB on
5/24/07.

So for May, he was under ContractA for 23 days, and then was moved to
ContractB for the last 8 days. Client is counted once, under longest
contract, so ContractA stays at 30 Clients Served, and under
ContractB, 15 Clients Served becomes 14

This means we have
30 Clients Served under ContractA
14 Clients Served under ContractB.
44 total

Part 2:
Client Y was admitted on 5/1/07 under ContractA, and was "moved" to
ContractB on 5/3/07. I've handled this in Access by giving the client
a "pretend" DischargeDate of 5/3/07, with a DischargeReason of
"Contract Change" and "admitting" him again under ContractB on
5/3/07.

So for May, he was under ContractA for 2 days, and then was moved to
ContractB for the last 29 days. Client is counted once, under longest
contract, so ContractA, 30 becomes 29, and ContractB stays at 14.

This means we have
29 Clients Served under ContractA
14 Clients Served under ContractB.
43 total

How do I get this final result in my report in Access?

I know I can have the text box on the report say {visible only if
DischargeReason is not "Contact Change"} but that might make the
longer instance invisible, simply because the longer instance came
first.

Any ideas? I'm lost.

Thanks,
Liz

Greetings All,

I haven't seen any responses yet. Is my problem too complicated?
Please let me know.

Thanks,
Liz