|
isqureshi66
Starting Member
1 Post |
 Posted - 2012-03-02 : 11:54:50
|
| I have a master table TEST_REPORTS_MT having fields:Report_No,Product_Name,Test_Date Sample data is:TEST_REPORTS_MTReport_No Product_Name Test_DateTR-1 Pr1 1-12-2011TR-10 Pr1 5-1-2012TR-2 Pr2 10-12-2011TR-3 Pr3 15-12-2011TR-4 Pr5 15-2-2012TR-5 Pr2 18-12-2011TR-6 Pr4 20-12-2011TR-7 Pr1 22-12-2011TR-8 Pr5 1-1-2012TR-9 Pr4 2-1-2012Detail table is TEST_REPORTS_DET having fields:ReportDet_No,Report_No_FK,Serial_No,Qty,ResultTEST_REPORTS_DETReportDet Report_ID Serial_Nos Qty Result1 TR-1 100,101,102 3 Qualified2 TR-1 103 1 Fail11 TR-10 20,21,22,23,24 5 In Process3 TR-2 10~19 10 Qualified12 TR-2 20~29 10 Qualified4 TR-3 F1,F2,F3 3 Fail15 TR-4 S1,S2,S3,S4,S5 5 Qualified16 TR-4 S6,S7,S8,S9,S10 5 Qualified17 TR-4 S11,S12,S13,S14,S15 5 Qualified5 TR-5 P1,P2,P3,P4,P5 5 Qualified6 TR-6 R1,R2,R3,R4,R5 5 R&D13 TR-6 R6,R7,R8,R9,R10 5 R&D14 TR-6 R11,R12,R13 3 R&D7 TR-7 R6~R15 10 R&D8 TR-8 F4,F5 2 Fail9 TR-8 P10,P11,P12 3 Qualified10 TR-9 F6,F7 2 FailRequirement:We need to count the Report_No from table TEST_REPORTS_MT group by TEST_REPORTS_DET.ResultThere 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 Mix3 2 2 1 2The 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. |
|
Access 2003 and VB6