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.