Hello,
I am using an ADO query and I am want to create an SQL statement to join two tables. One table list all of the training subjects and the other lists the employees, the date trained, and the primary ID of the training subject. I want to create a report that will list all of the training subjects and fill in the information where the employee has been trained. If I join the tables in a view and filter the records by employee ID, the query returns only the records where the employee table and the Training Subject table match. I started to create a nested SQL statement that would filter the Employee records to show only those for a specific employee, then use the results to link with the Training Subject table where the primary and foreign key IDs are the same, but show all records for the Training Subject table regardless if there is a match with the Employee table. I am not sure if this is the best way of doing it, but here is what I started (I keep getting a syntax error from the brackets around the SELECT statement in the WHERE clause).
qryTrainingRpt.close;
qryTrainingRpt.SQL.Clear;
qryTrainingRpt.Sql.Add('SELECT * FROM tbl_Training_SubjectData');
qryTrainingRpt.Sql.Add('RIGHT JOIN vw_Training_Employee ON tbl_Training_SubjectData.pri_Trng_ID = vw_Training_Employee.fnk_Trng_ID');
qryTrainingRpt.Sql.Add('WHERE (SELECT * FROM vw_Training_Employee WHERE fnk_Empl_ID = 13)');
qryTrainingRpt.open;
I broke the code up here to make it a little easier to read, and I hard coded the Employee ID in so I can test for the results. Later I am going to change it to a variable where I will pass in the parameter.
I would appreciate any assitance with this problem, and if anyone has a better way of doing this I am would appreciate any advice as well.
I am using an ADO query and I am want to create an SQL statement to join two tables. One table list all of the training subjects and the other lists the employees, the date trained, and the primary ID of the training subject. I want to create a report that will list all of the training subjects and fill in the information where the employee has been trained. If I join the tables in a view and filter the records by employee ID, the query returns only the records where the employee table and the Training Subject table match. I started to create a nested SQL statement that would filter the Employee records to show only those for a specific employee, then use the results to link with the Training Subject table where the primary and foreign key IDs are the same, but show all records for the Training Subject table regardless if there is a match with the Employee table. I am not sure if this is the best way of doing it, but here is what I started (I keep getting a syntax error from the brackets around the SELECT statement in the WHERE clause).
qryTrainingRpt.close;
qryTrainingRpt.SQL.Clear;
qryTrainingRpt.Sql.Add('SELECT * FROM tbl_Training_SubjectData');
qryTrainingRpt.Sql.Add('RIGHT JOIN vw_Training_Employee ON tbl_Training_SubjectData.pri_Trng_ID = vw_Training_Employee.fnk_Trng_ID');
qryTrainingRpt.Sql.Add('WHERE (SELECT * FROM vw_Training_Employee WHERE fnk_Empl_ID = 13)');
qryTrainingRpt.open;
I broke the code up here to make it a little easier to read, and I hard coded the Employee ID in so I can test for the results. Later I am going to change it to a variable where I will pass in the parameter.
I would appreciate any assitance with this problem, and if anyone has a better way of doing this I am would appreciate any advice as well.