count records in master table condition in child table

[isqureshi66]

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.

 

[Beetle]

Try a crosstab query of TEST_REPORTS_DET using Report_ID as Row Heading, Result as Column Heading and Qty as Value.

 

[isqureshi66]

Try a crosstab query of TEST_REPORTS_DET using Report_ID as Row Heading, Result as Column Heading and Qty as Value.

Please write the query text according to given tables and check whether output is same as given in the question.thanx

 

[Beetle]

Please write the query text according to given tables and check whether output is same as given in the question.thanx

Have you made any attempt to do this yourself, or are you just expecting others to do all your work for you?

I gave you the basic structure for setting up the crosstab. Use the Query Wizard if you need more help. As far as the results, only you can decide if it ultimately gives you the results you want or not.