Produce A Report Merging records from multiple tables
I think I'm at the final hurdle with my db that records staff performance and training records.
I basically have these tables:
Which records the details of a member of staff with fields for:
Which records details of any training given and to whom it was given to with fields for:
Which records any errors staff have made on a project with fields for:
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.