isqureshi66
New member
- Local time
- Today, 08:17
- Joined
- Mar 10, 2012
- Messages
- 2
I have a master table TEST_REPORTS_MT having fields:
Report_No,Product_Name,Test_Date
Sample data is:
TEST_REPORTS_MT
Report_No Product_Name Test_Date
TR-1 Pr1 1-12-2011
TR-10 Pr1 5-1-2012
TR-2 Pr2 10-12-2011
TR-3 Pr3 15-12-2011
TR-4 Pr5 15-2-2012
TR-5 Pr2 18-12-2011
TR-6 Pr4 20-12-2011
TR-7 Pr1 22-12-2011
TR-8 Pr5 1-1-2012
TR-9 Pr4 2-1-2012
Detail table is TEST_REPORTS_DET having fields:
ReportDet_No,Report_No_FK,Serial_No,Qty,Result
TEST_REPORTS_DET
ReportDet Report_ID Serial_Nos Qty Result
1 TR-1 100,101,102 3 Qualified
2 TR-1 103 1 Fail
11 TR-10 20,21,22,23,24 5 In Process
3 TR-2 10~19 10 Qualified
12 TR-2 20~29 10 Qualified
4 TR-3 F1,F2,F3 3 Fail
15 TR-4 S1,S2,S3,S4,S5 5 Qualified
16 TR-4 S6,S7,S8,S9,S10 5 Qualified
17 TR-4 S11,S12,S13,S14,S15 5 Qualified
5 TR-5 P1,P2,P3,P4,P5 5 Qualified
6 TR-6 R1,R2,R3,R4,R5 5 R&D
13 TR-6 R6,R7,R8,R9,R10 5 R&D
14 TR-6 R11,R12,R13 3 R&D
7 TR-7 R6~R15 10 R&D
8 TR-8 F4,F5 2 Fail
9 TR-8 P10,P11,P12 3 Qualified
10 TR-9 F6,F7 2 Fail
Requirement:
We need to count the Report_No from table TEST_REPORTS_MT group by TEST_REPORTS_DET.Result
There are 10 reports in total in TEST_REPORTS_MT table and count of them is as follows with respect to Result field in TEST_REPORTS_DET table.
Qualified Fail R&D In Process Qualified & Fail Mix
3 2 2 1 2
The query should be simple and do not contain UNION as the query will be imported in data environment of VB6 as a view and will be used as data source of report.
Report_No,Product_Name,Test_Date
Sample data is:
TEST_REPORTS_MT
Report_No Product_Name Test_Date
TR-1 Pr1 1-12-2011
TR-10 Pr1 5-1-2012
TR-2 Pr2 10-12-2011
TR-3 Pr3 15-12-2011
TR-4 Pr5 15-2-2012
TR-5 Pr2 18-12-2011
TR-6 Pr4 20-12-2011
TR-7 Pr1 22-12-2011
TR-8 Pr5 1-1-2012
TR-9 Pr4 2-1-2012
Detail table is TEST_REPORTS_DET having fields:
ReportDet_No,Report_No_FK,Serial_No,Qty,Result
TEST_REPORTS_DET
ReportDet Report_ID Serial_Nos Qty Result
1 TR-1 100,101,102 3 Qualified
2 TR-1 103 1 Fail
11 TR-10 20,21,22,23,24 5 In Process
3 TR-2 10~19 10 Qualified
12 TR-2 20~29 10 Qualified
4 TR-3 F1,F2,F3 3 Fail
15 TR-4 S1,S2,S3,S4,S5 5 Qualified
16 TR-4 S6,S7,S8,S9,S10 5 Qualified
17 TR-4 S11,S12,S13,S14,S15 5 Qualified
5 TR-5 P1,P2,P3,P4,P5 5 Qualified
6 TR-6 R1,R2,R3,R4,R5 5 R&D
13 TR-6 R6,R7,R8,R9,R10 5 R&D
14 TR-6 R11,R12,R13 3 R&D
7 TR-7 R6~R15 10 R&D
8 TR-8 F4,F5 2 Fail
9 TR-8 P10,P11,P12 3 Qualified
10 TR-9 F6,F7 2 Fail
Requirement:
We need to count the Report_No from table TEST_REPORTS_MT group by TEST_REPORTS_DET.Result
There are 10 reports in total in TEST_REPORTS_MT table and count of them is as follows with respect to Result field in TEST_REPORTS_DET table.
Qualified Fail R&D In Process Qualified & Fail Mix
3 2 2 1 2
The query should be simple and do not contain UNION as the query will be imported in data environment of VB6 as a view and will be used as data source of report.