Calculations in Reports

[harris]

I am designing a Housing Database to track rent payments for Non-For-
Profit organization and they calculate/figure the tenant/client's Rent
payment based on how many room mates. No Problem, Rent is set at apt
#1 for $750/month, 3 people share the apt so $750/3 yields $250 for
client #1, $250 for client #2, and $250 for client #3. Here is the
problem. They do not calculate rent based on equal payments for all
shared clients in one apartment, however they calculate based on how
much income each client gets from the State to find the weighted
percentage to contribute to the Total rent. Client making less money
should not be subject to same payments as client receiving more state
aid. (See example below)

Client #1 pulls $10,000 annually, so $10,000/(10,000+12,000+15,000) =>
27%
Client #2 pulls $12,000 annually, so $12,000 / (10000+12000+15000) =>
32%
Client #3 pulls $15,000 annually, so $15,000 / (10000+12000+15000) =>
41%

Taking the percentages from above I need to re-step thru the detail
section and calculate the rent portions for each client:
Client #1 .27 * $750 =$202.50
Client #2 .32 * $750 =$240.00
Client #3 .41 * $750 =$307.50

SO my issue with access is that I can setup a footer Group right below
the detail section and calculate this information with no problem.
wrong. The issue is to sum the annual figure in the group section,
but how do I step back and get the individual 10000, 12000, 15000
numbers...

I believe I have reached an issue that Access can not resolve...

Any recommendation in how to get the Rent portions for each Client in
a report would be greatly appreciated.

This database is completely done and just need to fix this issue.

thanks, Jeff Harris (ha****@compu-type.net)

[Mark]

What is your table structure as this info would help in resolving your
problem!!

Regards,

Mark

<ha****@compu-type.netwrote in message
news:49**********************************@a23g2000 hsc.googlegroups.com...

>I am designing a Housing Database to track rent payments for Non-For-
Profit organization and they calculate/figure the tenant/client's Rent
payment based on how many room mates. No Problem, Rent is set at apt
#1 for $750/month, 3 people share the apt so $750/3 yields $250 for
client #1, $250 for client #2, and $250 for client #3. Here is the
problem. They do not calculate rent based on equal payments for all
shared clients in one apartment, however they calculate based on how
much income each client gets from the State to find the weighted
percentage to contribute to the Total rent. Client making less money
should not be subject to same payments as client receiving more state
aid. (See example below)

Client #1 pulls $10,000 annually, so $10,000/(10,000+12,000+15,000) =>
27%
Client #2 pulls $12,000 annually, so $12,000 / (10000+12000+15000) =>
32%
Client #3 pulls $15,000 annually, so $15,000 / (10000+12000+15000) =>
41%

Taking the percentages from above I need to re-step thru the detail
section and calculate the rent portions for each client:
Client #1 .27 * $750 =$202.50
Client #2 .32 * $750 =$240.00
Client #3 .41 * $750 =$307.50

SO my issue with access is that I can setup a footer Group right below
the detail section and calculate this information with no problem.
wrong. The issue is to sum the annual figure in the group section,
but how do I step back and get the individual 10000, 12000, 15000
numbers...

I believe I have reached an issue that Access can not resolve...

Any recommendation in how to get the Rent portions for each Client in
a report would be greatly appreciated.

This database is completely done and just need to fix this issue.

thanks, Jeff Harris (ha****@compu-type.net)

[harris]

On May 13, 4:16*pm, "Mark" <[e-mail]:

What is your table structure as this info would help in resolving your
problem!!

Regards,

Mark

<[e-mail] in message

news:49**********************************@a23g2000 hsc.googlegroups.com...

I am designing a Housing Database to track rent payments for Non-For-
Profit organization and they calculate/figure the tenant/client's Rent
payment based on how many room mates. *No Problem, Rent is set at apt
#1 for $750/month, 3 people share the apt so $750/3 yields $250 for
client #1, $250 for client #2, and $250 for client #3. *Here is the
problem. *They do not calculate rent based on equal payments for all
shared clients in one apartment, however they calculate based on how
much income each client gets from the State to find the weighted
percentage to contribute to the Total rent. *Client making less money
should not be subject to same payments as client receiving more state
aid. (See example below)

Client #1 pulls $10,000 annually, so $10,000/(10,000+12,000+15,000) =>
27%
Client #2 pulls $12,000 annually, so $12,000 / (10000+12000+15000) =>
32%
Client #3 pulls $15,000 annually, so $15,000 / (10000+12000+15000) =>
41%

Taking the percentages from above I need to re-step thru the detail
section and calculate the rent portions for each client:
Client #1 * .27 * $750 =$202.50
Client #2 * .32 * $750 =$240.00
Client #3 * .41 * $750 =$307.50

SO my issue with access is that I can setup a footer Group right below
the detail section and calculate this information with no problem.
wrong. *The issue is to sum the annual figure in the group section,
but how do I step back and get the individual 10000, 12000, 15000
numbers...

I believe I have reached an issue that Access can not resolve...

Any recommendation in how to get the Rent portions for each Client in
a report would be greatly appreciated.

This database is completely done and just need to fix this issue.

thanks, Jeff Harris ([e-mail])- Hide quoted text -

- Show quoted text -

Client-tbl
ID Name Income

Building-tbl
Num Description State-adjustment Other Misc....

Apt-tbl
ID Building-tbl.Num Number NumClientsinApt
MonthlyRentforApt

ClientOccupancy-tbl (Assigns Client to Apt)
ID Building-tbl.Num Apt.ID Client.ID

Let me know what else you might need,

[Roger]

On May 13, 9:11*pm, "[e-mail]" <[e-mail]>
wrote:

On May 13, 4:16*pm, "Mark" <[e-mail]:

What is your table structure as this info would help in resolving your
problem!!

Regards,

Mark

<[e-mail] in message

news:49**********************************@a23g2000 hsc.googlegroups.com...

>I am designing a Housing Database to track rent payments for Non-For-
Profit organization and they calculate/figure the tenant/client's Rent
payment based on how many room mates. *No Problem, Rent is set at apt
#1 for $750/month, 3 people share the apt so $750/3 yields $250 for
client #1, $250 for client #2, and $250 for client #3. *Here is the
problem. *They do not calculate rent based on equal payments for all
shared clients in one apartment, however they calculate based on how
much income each client gets from the State to find the weighted
percentage to contribute to the Total rent. *Client making less money
should not be subject to same payments as client receiving more state
aid. (See example below)

Client #1 pulls $10,000 annually, so $10,000/(10,000+12,000+15,000) =>
27%
Client #2 pulls $12,000 annually, so $12,000 / (10000+12000+15000) =>
32%
Client #3 pulls $15,000 annually, so $15,000 / (10000+12000+15000) =>
41%

Taking the percentages from above I need to re-step thru the detail
section and calculate the rent portions for each client:
Client #1 * .27 * $750 =$202.50
Client #2 * .32 * $750 =$240.00
Client #3 * .41 * $750 =$307.50

SO my issue with access is that I can setup a footer Group right below
the detail section and calculate this information with no problem.
wrong. *The issue is to sum the annual figure in the group section,
but how do I step back and get the individual 10000, 12000, 15000
numbers...

I believe I have reached an issue that Access can not resolve...

Any recommendation in how to get the Rent portions for each Client in
a report would be greatly appreciated.

This database is completely done and just need to fix this issue.

thanks, Jeff Harris ([e-mail])- Hide quoted text -

- Show quoted text -

Client-tbl
ID * * * *Name * * *Income

Building-tbl
Num * * * *Description * * *State-adjustment * *Other Misc....

Apt-tbl
ID * * * *Building-tbl.Num * * *Number * * *NumClientsinApt
MonthlyRentforApt

ClientOccupancy-tbl (Assigns Client to Apt)
ID * * * *Building-tbl.Num * * Apt.ID * * * * Client.ID

Let me know what else you might need,- Hide quoted text -

- Show quoted text -

the queries below should give you want is needed for the report

client (id, name, income)
1 bill 10000
2 john 12000
3 mary 15000

building (id, desc)
1 a brown house
appt (id, buildingId, number, clients, rent)
1 1 1A 3 750
occupancy (id, buildingId, apptId, clientId)
1 1 1 1
1 1 1 2
1 1 1 3

qryOccupancyIncome
select apptId, sum(client.income) as totIncome
from occupancy inner join client on occupancy.clientId = client.id
group by apptId

1 37000
qryPercentShare
select clientId, income / totIncome as percentShare
from occupancy inner join qryOccupancyIncome on occupancy.apptId =
qryOccupancyIncome.apptId

1 .27
2 .32
3 .41
qryRentRequired
select clientId, appt.Rent * percentShare
from occupancy inner join qryPercentShare on occupancy.clientId =
qryPercentShare.clientId

1 202.50
2 240.00
3 307.50

[Analysis&Solutions]

As Roger mentioned, make queries that calculate the needed totals and
percentages. Then make one central query to use as the report source
that joins all of those queries.

--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409