I have a web application for recording time, bonuses and commissions for employees. I am having a hard time joining three tables together.
Here are the tables:
employee - provides employee information including the primary key emp_id
emp_timecard - provides recorded time from employees
tc_bon_com - provides bonuses and commissions for employee
I join employee with emp_timecard by joining on emp_id I then use a where statement to select only the records that are between a certain date range. This part works fine but where I run into a problem is when I throw the commission and bonus table into the mix. I need it to join the other tables using the emp_id field but I also need to only pull records that are in the pay_end_date range of the emp_timecard.
Here is my statement, what is happening now is that it is selecting records outside the date range;
sql_select = "" & _ " SELECT " & _ " E.first_name, E.last_name, E.userid, E.employee_status, E.filenumber, E.paygroup, C.date, C.day_in, " & _ " C.day_out, E.emp_id, E.employee_type, C.meal_out, C.meal_in, C.pto_hours, C.hol_hours, C.ber_hours, " & _ " C.chb_hours, C.special_hours, C.hours_worked, E.supervisorname, E.term_date, E.user_type_id, " & _ " T.bonus, T.commission, T.status " & _ " FROM employee E " & _ " LEFT JOIN emp_timecard C ON " & _ " E.userid=C.userid " & _ " LEFT JOIN tc_bon_com T ON " & _ " E.emp_id = T.emp_id " & _ " AND C.date BETWEEN '"&session("payp_week1_start")&"' AND '"&session("pay_end_date")&"' " & _ " ORDER BY E.supervisor_emp_id, E.employee_type, E.user_type_id, E.last_name, E.userid, C.date "
Can anyone point out where I am going wrong here? Thank you!