Complex Reports, multiple queries

[xdr3amx007]

Hello all,

I have a MySQL database that I am trying to generate reports for using Base. The Schema is as follows:

Cases: id, Case Name, Date Started, Date Ended, Closing Notes

• InvestigatorsCases: id, Case, Investigator
  • Investigators: id, Name, Employee ID
• Evidence Sources: id, Case, Acquired By, Acquired Date/Tme, Evidence Type, Location at Scene, Preservation Methods, Other Notes
  • Evidence Notes: id, Evidence Source, Investigator, Access Date/Time, Evidence Volume, Modified Date/Time, Creation Date/Time, Note
  • Generic Notes: id, Evidence Source, Time, Investigator, Note

Essentially, I want my Report Output to be formatted as:

Case
- Case Details
Investigators (relative to the case)
- Name1, ID1
- Name2, ID2
- Name3, ID2
Evidence1
- Details about Evidence 1 (relative to Evidence Source1)
- Evidence Notes Details 1 (relative to Evidence Source1)
- Generic Notes 1 (relative to Evidence Source1)
Evidence2
- Details about Evidence 2 (relative to Evidence Source2)
- Evidence Notes Details 2 (relative to Evidence Source2)
- Generic Notes 2 (relative to Evidence Source2)

My understanding is the stock reporting of Base isn't capable of this. While I can join all the the tables and produce a query that gives all the of the information, I would have unwanted redundant entries like:

Code: Select all

Case 1 Details, Investigator 1 details, Evidence 1 details, Evidence 1 notes, Generic notes 1
Case 1 Details, Investigator 1 details, Evidence 2 details, Evidence 2 notes, Generic notes 2
Case 1 Details, Investigator 1 details, Evidence 3 details, Evidence 3 notes, Generic notes 3
Case 1 Details, Investigator 2 details, Evidence 1 details, Evidence 1 notes, Generic notes 1
Case 1 Details, Investigator 2 details, Evidence 2 details, Evidence 2 notes, Generic notes 2
Case 1 Details, Investigator 2 details, Evidence 3 details, Evidence 3 notes, Generic notes 3
Case 1 Details, Investigator 3 details, Evidence 1 details, Evidence 1 notes, Generic notes 1
Case 1 Details, Investigator 3 details, Evidence 2 details, Evidence 2 notes, Generic notes 2
Case 1 Details, Investigator 3 details, Evidence 3 details, Evidence 3 notes, Generic notes 3
Case 2 Details, Investigator 1 details, Evidence 1 details, Evidence 1 notes, Generic notes 1
Case 2 Details, Investigator 1 details, Evidence 2 details, Evidence 2 notes, Generic notes 2
Case 2 Details, Investigator 1 details, Evidence 3 details, Evidence 3 notes, Generic notes 3
Case 2 Details, Investigator 2 details, Evidence 1 details, Evidence 1 notes, Generic notes 1
Case 2 Details, Investigator 2 details, Evidence 2 details, Evidence 2 notes, Generic notes 2
Case 2 Details, Investigator 2 details, Evidence 3 details, Evidence 3 notes, Generic notes 3

My sluething online has uncovered the Sun Report Builder, which apparently allows for more complex reporting. Unfortunately, I still haven't been able to figure out how to create this type of report, if anyone has hints or tips that will point me in the right direction, I would greatly appreciate it!