Query to pull dates from table
Posted: Mon Dec 17, 2018 3:57 pm
Hi
I have a database with two tables; 'Training Needs' and 'Training Log V2'. The Training Needs Table has a number of tick boxes, which are selected based on the trainees individual needs. The Training Log V2 is the table holding all training records.
I'd like to create a query which will give me the dates the training has been completed if the training needs entry is ticked for that person.
So far I have the following, but I'm getting a little stuck now. I've got the date for one of the training courses, but I don't know how to get the others without causing extra rows of data, which I could do, but then the report I want to create will have one date in each section. It's a little bit of a mess, so any help would be greatly appreciated.
Ultimately, I'm looking to create a report that looks like the following, with all of the dates of the training completed on the right hand side?:
Many thanks in advance.
I have a database with two tables; 'Training Needs' and 'Training Log V2'. The Training Needs Table has a number of tick boxes, which are selected based on the trainees individual needs. The Training Log V2 is the table holding all training records.
I'd like to create a query which will give me the dates the training has been completed if the training needs entry is ticked for that person.
So far I have the following, but I'm getting a little stuck now. I've got the date for one of the training courses, but I don't know how to get the others without causing extra rows of data, which I could do, but then the report I want to create will have one date in each section. It's a little bit of a mess, so any help would be greatly appreciated.
Code: Select all
SELECT "Training Needs"."FullName", "Training Needs"."Company", CASEWHEN( "AbrasiveWheels" = FALSE, 'No', 'Yes' ) AS "AbrasiveWheels", CASEWHEN( "AsbestosAwareness" = FALSE, 'No', 'Yes' ) AS "AsbestosAwareness", "Training Log V2"."TrainingDate", "Training Log V2"."CourseTitle", CASEWHEN( "AsbestosMedical" = FALSE, 'No', 'Yes' ) AS "AsbestosMedical", CASEWHEN( "CAT&Genny" = FALSE, 'No', 'Yes' ) AS "CAT&Genny", CASEWHEN( "CoreDrill" = FALSE, 'No', 'Yes' ) AS "CoreDrill", CASEWHEN( "FacefitFFP3" = FALSE, 'No', 'Yes' ) AS "FacefitFFP3", CASEWHEN( "FacefitSR100" = FALSE, 'No', 'Yes' ) AS "FacefitSR100", CASEWHEN( "Ladders" = FALSE, 'No', 'Yes' ) AS "Ladders", CASEWHEN( "ManualHandling" = FALSE, 'No', 'Yes' ) AS "ManualHandling", CASEWHEN( "NewStarterTrade" = FALSE, 'No', 'Yes' ) AS "NewStarterTrade", CASEWHEN( "NNLWAsbestosManual" = FALSE, 'No', 'Yes' ) AS "NNLWAsbestosManual", CASEWHEN( "PASMA" = FALSE, 'No', 'Yes' ) AS "PASMA", CASEWHEN( "Scaffold" = FALSE, 'No', 'Yes' ) AS "Scaffold", CASEWHEN( "SMSTS/SSSTS" = FALSE, 'No', 'Yes' ) AS "SMSTS/SSSTS", CASEWHEN( "TetraLevel3" = FALSE, 'No', 'Yes' ) AS "TetraLevel3" FROM "Training Needs", "Training Log V2" WHERE "Training Needs"."FullName" = "Training Log V2"."FullName" AND "Training Log V2"."CourseTitle" = 'Asbestos Awareness'
Many thanks in advance.