Kinda A Complicated Query Question

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14068-kinda-a-complicated-query-question.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
A 1 1 0
2
B 1 0 1
0

Etc....

Anybody got any ideas?

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

do you have options to use temp tables?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
wrote in message
news:1154024023.998590.188620@p79g2000cwp.googlegr oups.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
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
> Etc....
>
> Anybody got any ideas?
>

- 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] &
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
"'") 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068

- 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;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
CON: very slow if here's a lot of records;
PRO: 1 query!

- make multiply querys that count each scenario and join them together
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14068
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