Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Tricky (challenging?) sub-report problem

Discussion in 'Information Technology' started by Maury Markowitz, Jul 28, 2006.

  1. Maury Markowitz

    Maury Markowitz
    Expand Collapse
    Guest

    Here's my table...

    ticketId partId part
    10 105 gizifas
    10 106 whatzits
    10 107 thingamabobs
    11 108 dohickies

    Sadly, partId is the primary key (as you might have guessed) and is the only
    thing being stored in many other tables.

    I have a report that gathers up each ticket thus...

    SELECT * from tblTickets WHERE partId IN
    (select min(partId) from tblTickets group by ticketId)

    That works great. The problem is I need to have a subreport that shows all
    the parts of the ticket. Now this would be easy if the other tables had the
    ticketId, I'd just "join" on that. But the other table doesn't have the
    ticketId, only the partId (it's a table tracking auditing information, every
    change to every part).

    Can anyone suggest a way to do this? I can do it in raw SQL easily enough,
    but I simply don't know how to make a report that uses it.

    Maury
     
  2. Loading...

    Similar Threads Forum Date
    What is Jhatka? Tricky Reality... Hard Talk Feb 4, 2011

  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Maury.

    Maury Markowitz wrote:
    > Here's my table...
    >
    > ticketId partId part
    > 10 105 gizifas
    > 10 106 whatzits
    > 10 107 thingamabobs
    > 11 108 dohickies
    >
    > Sadly, partId is the primary key (as you might have guessed) and is
    > the only thing being stored in many other tables.
    >
    > I have a report that gathers up each ticket thus...
    >
    > SELECT * from tblTickets WHERE partId IN
    > (select min(partId) from tblTickets group by ticketId)


    This selects only those records with the lowest partID per ticketID.

    > That works great. The problem is I need to have a subreport that
    > shows all the parts of the ticket. Now this would be easy if the other
    > tables had the ticketId, I'd just "join" on that. But the other table
    > doesn't have the ticketId, only the partId (it's a table tracking
    > auditing information, every change to every part).


    So how about simply joining the other table using the partID field?
    You could use a single report that groups the records by ticketID,
    display the ticketID in the ticketID header section and display the
    parts information in the detail section.

    > Can anyone suggest a way to do this? I can do it in raw SQL easily
    > enough, but I simply don't know how to make a report that uses it.


    If you definitely want to use a subreport:
    Base the main report on "SELECT DISTINCT ticketID FROM tblTickets"
    Create another query that joins your other table to tblTickets on the
    partID field and base the subreport on that query.
    Link the reports on ticketID (LinkChildFields and LinkMasterFields).

    --
    Regards,
    Wolfgang
     

Share This Page