Alpha Video Training
Results 1 to 2 of 2

Thread: Report Expression = Getting a Count of records in Child Table

  1. #1
    Member
    Real Name
    Joe Lavely
    Join Date
    Jun 2013
    Posts
    484

    Default Report Expression = Getting a Count of records in Child Table

    I thought this would be simple, but it is not simple for me.

    I have a report with a detail section. The detail section fetches records from a parent MySQL table named "loads"

    There is a child table named "stops", and each load record has 2 or more children in the stops table.

    Each row in the report is a load. I want to display the number of stops for each load. This would be the count of matching child records.

    In the sql query for the report, if I join the stops table to the loads table, the report shows a row for every stop, so there are 2 or more rows.

    I am wanting one row per load, with a column that shows the number of stops.

  2. #2
    Member
    Real Name
    Joe Lavely
    Join Date
    Jun 2013
    Posts
    484

    Default Re: Report Expression = Getting a Count of records in Child Table

    I managed to get this done in the sql query for the report.

    SELECT loads.Load_ID,
    COUNT(stops.Loads_FK) AS NumberOfStops
    FROM loads
    LEFT OUTER JOIN stops stops
    ON stops.Loads_FK = loads.Load_ID
    WHERE loads.Carr_IDFK = :whatCarrierID
    Group BY loads.Load_ID


    This is the basic query that returns the count as NumberOfStops.

Similar Threads

  1. Count child records in set
    By Peter.Greulich in forum Alpha Five Version 11 - Desktop Applications
    Replies: 6
    Last Post: 02-25-2016, 11:01 PM
  2. Count child records
    By aprescott in forum Alpha Five Version 11 - Desktop Applications
    Replies: 8
    Last Post: 03-25-2013, 03:13 PM
  3. Replies: 20
    Last Post: 10-19-2006, 06:17 PM
  4. Can you count child records with out a filter?
    By Neil Hucker in forum Alpha Five Version 6
    Replies: 27
    Last Post: 08-01-2005, 05:37 PM
  5. Report loses order when Count expression added
    By Charlain in forum Alpha Five Version 5
    Replies: 4
    Last Post: 10-02-2003, 08:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •