Another Aging Query - Selling and Buying Patterns

[Woo Mun Foong]

I have the following table :-

CustID, TrxDate, TrxType, Amount
======================
CustA, 1-Jan-2005, Buy, 1000.00
CustA, 5-March-2005, Buy, 1500.00
CustA, 10-March-2005, Sell, 500.00
CustA, 10-May-2005, Sell, 100.00
CustA, 15-June-2005, Buy, 500.00
CustA, 30-June-2005, Buy, 500.00
CustA, 2-Aug-2005, Buy, 1500.00
CustA, 7-Aug-2005, Sell, 700.00
CustB, 30-Apr-2005, Buy, 500.00
CustB, 2-July-2005, Buy, 1500.00
CustB, 7-Aug-2005, Sell, 700.00

I need a query that return something like the followings:-
CustID, TrxType,Current mth, 1mth, 2mth, 3mth, 4mth, >5mth, Average(6mth)
==================================
CustA, Buy, 1500, 0, 1000, 0, 0, 2500, 666.67
CustA, Sell, 700, 0, 0, 100, 0, 500, 216.67
CustB, Buy, 0, 1500, 0, 0, 500, 0, 333.33
CustB, Sell, 700, 0, 0, 0, 0, 0, 116.67

Anyone help ?

 

[[MVP] S.Clark]

The short answer is that you need to create a crosstab query.

The hard part then becomes the sliding scale from month to month. Examine
how a crosstab works, then feel free to post more questions.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting