These are the only statuses in your Form1 table???
Status |
|
Decided |
Pending |
Transfer |
And these are your stages
Stage |
Agreement |
Arguments |
Attendence |
CDR |
Copies |
Evidence |
Habious |
Latest |
Mafroor |
New commer |
Order |
Process |
Remaining Evidence |
Report173 |
Starting Arguments |
Statement |
Submition |
Summon |
طلبی ملزم |
فرد جرم |
مزیدکاروائی |
نوٹس |
نوٹس/طلبی |
ہمراہ مسل |
Try this and see if it's close to what you are seeking.
Code:
TRANSFORM Count(Form1.[ID]) AS CountOfID
SELECT Form1.[Stage], Count(Form1.[ID]) AS [Total at Stage]
FROM Form1 where status is not null
GROUP BY Form1.[Stage]
PIVOT Form1.[Status];
Result:
Stage |
Total at Stage |
Decided |
Pending |
Transfer |
Agreement |
2 |
2 |
|
|
Arguments |
195 |
191 |
1 |
3 |
Attendence |
2 |
2 |
|
|
Copies |
1 |
1 |
|
|
Evidence |
81 |
50 |
23 |
8 |
Habious |
23 |
23 |
|
|
Latest |
75 |
74 |
|
1 |
Mafroor |
4 |
3 |
1 |
|
New commer |
1 |
1 |
|
|
Order |
5 |
3 |
|
2 |
Process |
2 |
2 |
|
|
Remaining Evidence |
2 |
1 |
|
1 |
Report173 |
19 |
18 |
1 |
|
Starting Arguments |
3 |
3 |
|
|
Statement |
3 |
|
1 |
2 |
Submition |
1 |
1 |
|
|
Summon |
145 |
136 |
4 |
5 |
طلبی ملزم |
21 |
19 |
|
2 |
فرد جرم |
44 |
37 |
6 |
1 |
مزیدکاروائی |
3 |
3 |
|
|
نوٹس |
1 |
1 |
|
|
نوٹس/طلبی |
1 |
1 |
|
|
ہمراہ مسل |
10 |
6 |
3 |
1 |
Good luck