Board index » delphi » Tricky Local SQL Problem ?????

Tricky Local SQL Problem ?????

Hi,

I've got a tricky (or least I find it tricky) problem that I must get done in
Delphi 16bit using Local SQL (Paradox Tables) . It's probably best explained
by some sample data.

The source table contains records of people who have subscribed to a magazine.
This table holds their Membership ID, the Subscription Type (e.g. 1 Month, 3
Month and 6 Month), the status of their subscription (i.e. ACTIVE, CANCELLED
and RENEWAL).

What I want is a table that gives me the number of each status
for each Subscription type. For example....

***** Source Table (PEOPLE.DB) *****

MemID           SubscriptionType            Status
-----------------------------------------------------------------------------
  1                     1 MONTH                     ACTIVE
  2                     3 MONTH                     ACTIVE
  3                     1 MONTH                     RENEWAL
  4                     3 MONTH                     ACTIVE  
  5                     3 MONTH                     ACTIVE
  6                     1 MONTH                     CANCELLED
  7                     6 MONTH                     ACTIVE
  8                     3 MONTH                     CANCELLED

I want to generate a table like this......

SubscriptType     ACTIVE   CANCELLED    RENEWAL
-------------------------------------------------------------------------------
1 MONTH                  1                  1                     1
3 MONTH                  3                  1                     0
6 MONTH                  1                  0                     0

Please help I've just run our of ideas !!

Thanks in advance !!!!

 

Re:Tricky Local SQL Problem ?????


I'm definately no SQL expert, but I think this is a cross-tab query... I've
seen several freeware components floating around that generates these in
Delphi (you didn't state if your working in D1, or D2??). I know you can find
at least one of these at the Delphi Super Page.

Good luck,

Peter.

-----------------------
In article <webmaster.162.02C72...@wildthing.lone-wolf-net.com>,

Quote
webmas...@wildthing.lone-wolf-net.com (David Blackwater) wrote:
>The source table contains records of people who have subscribed to a magazine.
>This table holds their Membership ID, the Subscription Type (e.g. 1 Month, 3
>Month and 6 Month), the status of their subscription (i.e. ACTIVE, CANCELLED
>and RENEWAL).

>What I want is a table that gives me the number of each status
>for each Subscription type. For example....

>***** Source Table (PEOPLE.DB) *****

>MemID           SubscriptionType            Status
>-----------------------------------------------------------------------------
>  1                     1 MONTH                     ACTIVE
>  2                     3 MONTH                     ACTIVE
>  3                     1 MONTH                     RENEWAL
>  4                     3 MONTH                     ACTIVE  
>  5                     3 MONTH                     ACTIVE
>  6                     1 MONTH                     CANCELLED
>  7                     6 MONTH                     ACTIVE
>  8                     3 MONTH                     CANCELLED

>I want to generate a table like this......

>SubscriptType     ACTIVE   CANCELLED    RENEWAL
>-------------------------------------------------------------------------------
>1 MONTH                  1                  1                     1
>3 MONTH                  3                  1                     0
>6 MONTH                  1                  0                     0

*************************************************************************
* Peter G. Millard      ******************* Maintainer of the VB        *
* mill...@buffnet.net          **************  General & VBDOS FAQ's      *
* http://www.buffnet.net/~millard  ******** Error Reading Drive A.....      *
* http://www.vantek-corp.com             **     Formatting C: Instead    *
*************************************************************************

Other Threads