We're in the process of converting an Access DB to Salesforce, a lot of our reports are built from querys of querys. For example, we want to produce a report that shows some fields from the parent record plus fields from 2 child objects (but only the record where a specified date field on the child is the most recent date).
Previously, we just queried out the most recent records and then just joined them to main query. Our consultants have sort of got around the issue by adding the required fields to the parent object and then setting up a trigger to copy the data from the child to the parent, however, im not convinced this is the best solution.
Would be very grateful for any suggestions as to how to deal with this in SF or whether our consultants sol is the best option.
It sounds like you're talking about using nested SOQL queries. Here's an example of querying a parent and two child objects in one query, using the relationship name for each related list of objects:
You can then loop through those Accounts in Apex, and for each one, there is a list (size 0 or 1) of Tasks and Custom_Object__c: