Results 1 to 6 of 6
  1. #1
    isqureshi66 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    3

    count records in master table condition in child table

    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.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One issue appears to be the pk/fk relationship of Result_ID between Results_MT and Test_Reports_Det. The ID is an autonumber pk but the fk is set as text. You have a query that attempts to join on these fields. However, Test_Reports_Det is saving the actual Result text, not the ID. This results in datatype missmatch error. The Result_MT table is not needed in the query. Remove it.

    Also, don't understand your count results. I see the following in the data:
    Qualified Fail R&D In Process Qual & Fail Mix
    5 4 2 1 2

    The Mix value is the most difficult because that involves evaluating data from multiple records. Review this http://allenbrowne.com/subquery-01.html

    This nested query returns the above numbers except for the Mix value:

    SELECT Count(Query1.Qual) AS CountOfQual, Count(Query1.Fail) AS CountOfFail, Count(Query1.RD) AS CountOfRD, Count(Query1.InProc) AS CountOfInProc
    FROM
    (SELECT DISTINCT TEST_REPORTS_DET.Report_ID, IIf([Result]="Qualified",[Result],Null) AS Qual, IIf([Result]="Failed",[Result],Null) AS Fail, IIf([Result]="R&D",[Result],Null) AS RD, IIf([Result]="In Process",[Result],Null) AS InProc
    FROM TEST_REPORTS_DET) As Query1;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isqureshi66 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    3
    Real problem is with the count of count of "Qualified & Fail Mix" otherwise its not so difficult.
    There are only 10 records in master table
    Count of records is as follows
    Qualified Fail R&D In Process Qualified & Fail Mix
    3 2 2 1 2

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I see now, you don't want Report_ID counted in either Qualified or Fail if it is in both, count only in the Mix. That really complicates because requires evaluating data from multiple records. The question for each Report_ID is: Does this ID have records in both Qualified and Fail? This means a subquery or DLookup or call to a function that will evaluate all the records to return a Yes or No. If Yes then don't count under Qualified or Fail, just Mix. This question would have to applied to each of these three category constructed fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isqureshi66 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    3
    Please have a look at attached database and send it back to me after creating the required query.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That effort could involve more than a query. It might mean writing code. Not simple code and not something I want to take on. You need to attempt a solution and post effort for analysis if you encounter issues.

    See if this helps http://allenbrowne.com/subquery-01.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-31-2011, 07:03 AM
  2. Count records in another table
    By jonnyuk3 in forum Forms
    Replies: 2
    Last Post: 02-23-2011, 04:46 AM
  3. Replies: 10
    Last Post: 01-17-2011, 10:47 PM
  4. Master/Child between Subforms
    By Pilotwings_64 in forum Forms
    Replies: 3
    Last Post: 08-22-2010, 01:45 AM
  5. Pulling most recent data from child table
    By davidv43 in forum Access
    Replies: 0
    Last Post: 02-07-2009, 11:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums