Re: Another Aging Query - Selling and Buying Patterns

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
Consulting@xxxxxxxxxx
www.fmsinc.com/consulting

"Woo Mun Foong" <mfwoo@xxxxxxxxx> wrote in message
news:7CFE57E9-0FF4-4AC2-9F59-2E0A3AC36DC5@xxxxxxxxxxxxxxxx
>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 ?


.

Relevant Pages