30/60/90/120 Aging

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!!!!!!!!!
 
M

Michel Walsh

Hi,


The first query sounds to be a GROUP BY query, or a total query. The
GROUP(s) can be from an expression.

The second query is a CROSSTAB query, where the PARTITION function can be
used. See the help file about Partition.

The last part sounds to be the job of a report, where you group by on
client, then on expression (ranges of dates), and print the details, for
each group.


Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top