G
Gary B
Help! I can not solve this. Must be SQL, as I connect through Jet.
Pmts not fully Applied (such as clients who prepay), must be accounted for.
Database structure:
tblClients:
ClientsKey
ClientName
tblCharges:
ChargesKey
ClientsKey
ChargesDate
tblServices:
ServicesKey
ChargesKey
ClientsKey
ItemsKey
ServiceAmount
tblItems:
ItemsKey
ItemsDescription
tblPayments:
PaymentsKey
ClientsKey
PaymentsDate
tblApplies:
PaymentsKey
ServicesKey
ClientsKey
ApplyAmount
To start, I need to do a Summary as follows:
Days Total
----------- ---------
01 - 30 $0.00
31 - 60 $0.00
61 - 90 $0.00
91 - 120 $0.00
Over 120 $0.00
Then, I need a Client Summary as follows:
Client Name 01 - 30 31 - 60 61 - 90 91 - 120 Over 120 Totals
------------- --------- -------- -------- --------- ---------- ---------
Smith, John $0.00 $0.00 $0.00 $0.00 $0.00
$0.00
Jones, Frank $0.00 $0.00 $0.00 $0.00 $0.00
$0.00
Then, I need a Client Detail as follows:
Date Description Charge Balance
-------- ----------------- -------- -----------
Smith, John
01 - 30
31 - 60
06/01/05 Cleaned Fence $0.00 $0.00
06/22/05 Painted Garage $0.00 $0.00
61 - 90
91 - 120
Over 120
Jones, Frank
01 - 30
07/10/05 Cleaned Fence $0.00 $0.00
07/22/05 Painted Garage $0.00 $0.00
31 - 60
61 - 90
91 - 120
Over 120
THANK YOU!!!!!!!!!
Pmts not fully Applied (such as clients who prepay), must be accounted for.
Database structure:
tblClients:
ClientsKey
ClientName
tblCharges:
ChargesKey
ClientsKey
ChargesDate
tblServices:
ServicesKey
ChargesKey
ClientsKey
ItemsKey
ServiceAmount
tblItems:
ItemsKey
ItemsDescription
tblPayments:
PaymentsKey
ClientsKey
PaymentsDate
tblApplies:
PaymentsKey
ServicesKey
ClientsKey
ApplyAmount
To start, I need to do a Summary as follows:
Days Total
----------- ---------
01 - 30 $0.00
31 - 60 $0.00
61 - 90 $0.00
91 - 120 $0.00
Over 120 $0.00
Then, I need a Client Summary as follows:
Client Name 01 - 30 31 - 60 61 - 90 91 - 120 Over 120 Totals
------------- --------- -------- -------- --------- ---------- ---------
Smith, John $0.00 $0.00 $0.00 $0.00 $0.00
$0.00
Jones, Frank $0.00 $0.00 $0.00 $0.00 $0.00
$0.00
Then, I need a Client Detail as follows:
Date Description Charge Balance
-------- ----------------- -------- -----------
Smith, John
01 - 30
31 - 60
06/01/05 Cleaned Fence $0.00 $0.00
06/22/05 Painted Garage $0.00 $0.00
61 - 90
91 - 120
Over 120
Jones, Frank
01 - 30
07/10/05 Cleaned Fence $0.00 $0.00
07/22/05 Painted Garage $0.00 $0.00
31 - 60
61 - 90
91 - 120
Over 120
THANK YOU!!!!!!!!!