J
Joe Cilinceon
What I'm trying to do is print out a report that will list everything
collected on a given account. The tables that the query for the report would
be built on is as follows:
TENANTS contains account name
CustNo is a unique #
LEASES Contains Unit Number rented, Movein Date, Moveout Date etc
LeaseID is a unique text file made up of the Custno combined with the
unitno and the date of movein. Will refer to this as account number for now.
LEDGER Includes DatePaid, PaidFrom and PaidThru, RentRate at time of
payment, and a break down of all other charges such as late fees.
LeaseID to tie it to an account number
Transaction auto number for each payment record.
PAYMENTS Amount Paid, Method of Payment and Tracking # for check or Credit
Card payment. Can have more than 1 record with the same transaction number
for cases of split payments.
Transaction ties to Ledger with this.
In the Header I want the Tenant's Name, Unit #, MoveIn Date, MoveOut Date if
applicable.
Group by Payment Date and on group header want Payment Date, transaction #,
Amount Paid, payment method and PaidThru date if applicable.
Detail I would like to only print charges and skip those that are 0 or null.
I've been able to get the grosses without much problem it is the break down
that is giving me trouble.
Example of layout I'm looking for:
Tenant's Ledger
Account Num. Account Name Unit
MoveIn MoveOut
12301020051005 John Doe 010
10/05/2005 (here if Null then blank)
Payment
Date Transaction # Total Amount Method
Tracking Paid Thru
11/1/2005 12110000011 $100.00 Check
1124 11/30/05
Rent: 90.00
Lock 10.00
10/5/2005 10100001111 $98.00 VISA
10101 10/31/05
Rent: 88.00
Adm. Fee 10.00
Grand Total $198.00
collected on a given account. The tables that the query for the report would
be built on is as follows:
TENANTS contains account name
CustNo is a unique #
LEASES Contains Unit Number rented, Movein Date, Moveout Date etc
LeaseID is a unique text file made up of the Custno combined with the
unitno and the date of movein. Will refer to this as account number for now.
LEDGER Includes DatePaid, PaidFrom and PaidThru, RentRate at time of
payment, and a break down of all other charges such as late fees.
LeaseID to tie it to an account number
Transaction auto number for each payment record.
PAYMENTS Amount Paid, Method of Payment and Tracking # for check or Credit
Card payment. Can have more than 1 record with the same transaction number
for cases of split payments.
Transaction ties to Ledger with this.
In the Header I want the Tenant's Name, Unit #, MoveIn Date, MoveOut Date if
applicable.
Group by Payment Date and on group header want Payment Date, transaction #,
Amount Paid, payment method and PaidThru date if applicable.
Detail I would like to only print charges and skip those that are 0 or null.
I've been able to get the grosses without much problem it is the break down
that is giving me trouble.
Example of layout I'm looking for:
Tenant's Ledger
Account Num. Account Name Unit
MoveIn MoveOut
12301020051005 John Doe 010
10/05/2005 (here if Null then blank)
Payment
Date Transaction # Total Amount Method
Tracking Paid Thru
11/1/2005 12110000011 $100.00 Check
1124 11/30/05
Rent: 90.00
Lock 10.00
10/5/2005 10100001111 $98.00 VISA
10101 10/31/05
Rent: 88.00
Adm. Fee 10.00
Grand Total $198.00