Tricky (challenging?) sub-report problem

[Maury Markowitz]

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

 

[Wolfgang Kais]

Hello Maury.

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).