Kinda a complicated query question

  • Thread starter Thread starter mmohon
  • Start date Start date
M

mmohon

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?
 
- 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
 
- 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
 
Back
Top