Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Kinda A Complicated Query Question

Discussion in 'Information Technology' started by mmohon@gmail.com, Jul 28, 2006.

  1. mmohon@gmail.com

    mmohon@gmail.com
    Expand Collapse
    Guest

    I've been racking my brain about this one, and am trying to find the
    simplest solution to the problem.

    My table looks like this:

    Key Sip1A Sip1B Sip1C CaseType
    1234 E D E A
    1235 E E E B
    1236 D D D A

    E means they met the goal, D means they didn't meet the goal.

    I want to rearrange things like this....

    Group by Case Type and Count based on the values E and D....

    CaseType Sip1AMet Sip1ANotMet Sip1BMet Sip1BNotMet
    A 1 1 0
    2
    B 1 0 1
    0

    Etc....

    Anybody got any ideas?
     
  2. Loading...

    Similar Threads Forum Date
    Christianity Catholic Saint Selection: It's Complicated Interfaith Dialogues May 1, 2011

  3. prisoft

    prisoft
    Expand Collapse
    Guest

    do you have options to use temp tables?
    <mmohon@gmail.com> wrote in message
    news:1154024023.998590.188620@p79g2000cwp.googlegroups.com...
    > I've been racking my brain about this one, and am trying to find the
    > simplest solution to the problem.
    >
    > My table looks like this:
    >
    > Key Sip1A Sip1B Sip1C CaseType
    > 1234 E D E A
    > 1235 E E E B
    > 1236 D D D A
    >
    > E means they met the goal, D means they didn't meet the goal.
    >
    > I want to rearrange things like this....
    >
    > Group by Case Type and Count based on the values E and D....
    >
    > CaseType Sip1AMet Sip1ANotMet Sip1BMet Sip1BNotMet
    > A 1 1 0
    > 2
    > B 1 0 1
    > 0
    >
    > Etc....
    >
    > Anybody got any ideas?
    >
     
  4. ti976@yahoo.com

    ti976@yahoo.com
    Expand Collapse
    Guest

    - Group Casetype and use dcount function , in SQL (Where test is table
    name):
    SELECT test.CaseType, DCount("[SipA]","test","[SipA] = 'E' AND
    [CaseType]='" & [CaseType] & "'") AS Asip,
    DCount("[SipA]","test","[SipA] = 'D' AND [CaseType]='" & [CaseType] &
    "'") AS nAsip, DCount("[SipB]","test","[SipB] = 'E' AND [CaseType]='" &
    [CaseType] & "'") AS Bsip, DCount("[SipB]","test","[SipB] = 'D' AND
    [CaseType]='" & [CaseType] & "'") AS nBsip
    FROM test
    GROUP BY test.CaseType;
    CON: very slow if here's a lot of records;
    PRO: 1 query!

    - make multiply querys that count each scenario and join them together
    by CaseType
    CON: maintaining multiple queries;
    PRO: easier to concept - should be faster than above for lots of
    records

    - temp table isn't bad either - just run a bunch of append/update
    queries.
    CON: maintaining multiple queries;
    PRO: easier to concept - should be faster than above for lots of records
     
  5. ti976@yahoo.com

    ti976@yahoo.com
    Expand Collapse
    Guest

    - Group Casetype and use dcount function , in SQL (Where test is table
    name):
    SELECT test.CaseType, DCount("[SipA]","test","[SipA] = 'E' AND
    [CaseType]='" & [CaseType] & "'") AS Asip,
    DCount("[SipA]","test","[SipA] = 'D' AND [CaseType]='" & [CaseType] &
    "'") AS nAsip, DCount("[SipB]","test","[SipB] = 'E' AND [CaseType]='" &
    [CaseType] & "'") AS Bsip, DCount("[SipB]","test","[SipB] = 'D' AND
    [CaseType]='" & [CaseType] & "'") AS nBsip
    FROM test
    GROUP BY test.CaseType;
    CON: very slow if here's a lot of records;
    PRO: 1 query!

    - make multiply querys that count each scenario and join them together
    by CaseType
    CON: maintaining multiple queries;
    PRO: easier to concept - should be faster than above for lots of
    records

    - temp table isn't bad either - just run a bunch of append/update
    queries.
    CON: maintaining multiple queries;
    PRO: easier to concept - should be faster than above for lots of records
     

Share This Page