2 Replies Latest reply on Oct 28, 2009 3:56 AM by Peakoverload

    Produce A Report Merging records from multiple tables

    Peakoverload

      Title

      Produce A Report Merging records from multiple tables

      Post

      I think I'm at the final hurdle with my db that records staff performance and training records.

       

      I basically have these tables:

       

      StaffDetails:

      Which records the details of a member of staff with fields for:

      Name

      EmployeeID

      Joined

      Left

      Picture

      Job

      Notes

       

      Training:

      Which records details of any training given and to whom it was given to with fields for:

      Training

      Date

      Training ID

       

      Defects:

      Which records any errors staff have made on a project with fields for:

      Book Title

      Book Number

      Date

      EmployeeID

      Defect
      Retraining Required

       

      I also have the following relationships:

      StaffDetails::EmployeeID = Defects::EmployeeID

      StaffDetails::EmployeeID = StaffTraining::EmployeeID

      Training::TrainingID = StaffTraining::TrainingID

       

       

      Everything, I hope is working now as it should. However I need to be able to produce a report where I can select any member of staff and see in chronoligical order any defects records and any training records that apply to them.

       

      For example if Joe Bloggs received training on 1/1/09 (UK date format) on process A, then made a mistake on a project on 1/5/09 which required retraining, training was given on 3/5/09, they then made another mistake on 3/8/09 but this didn't require retraining.

       

      I would see all of this presented as say a list in chronological order and ideally I'd like to also be able to specify a date range as well as being able to select for all time.

       

      I'm not sure what the best way is to achieve this. In playing around I managed to get a report to generate a report of sorts but it didn't sort anything by date order and instead was based on the order of the fields that I had placed on the layout and even then seemed to only display one record from each table.

       

      Any ideas?

        • 1. Re: Produce A Report Merging records from multiple tables
          philmodjunk
            

          BTW, when you listed your tables, you left out the join table: Staff_Training :smileywink:

           

          Two options come to mind:

           

          Option1: If every instance of staff training that you want to see in your report is preceded by a Defect record...

          Add a link to the Staff_Training table so that you can display any training events associated with a given defect.

          Base your layout on the Defects table and add a portal to display related Staff_Training records. You can set the portal to shrink and reduce the body part to eliminate blank spaces in your report.

           

          Option 2: Merge your Staff_Training and Defects records into a single table. You might be able to redesign your database to make this a permanent change, or you could set up a temporary table and use import records to copy records from Staff_Training and Defects into the temp table. Now base your report on the contents of this temporary table.

          • 2. Re: Produce A Report Merging records from multiple tables
            Peakoverload
              

            Cheers Phil!

             

            Option 1 wouldn't work for me I think because training would actually rarely be preceeded by a defect record. This is because our production process is regularly changed and so training is often required but, thankfully, the training is very good and so there aren't too many defect records.

             

            Option 2 sounds the more likely approach. As I have only created dummy test records so far I'm guessing that merging the tables now would be better than creating a temporary table.

             

            Why do I get the feeling that things are about to go horribly wrong!