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
Requirement:
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
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.
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.
Qualified Fail R&D In Process Qualified & Fail Mix 3 2 2 1 2