February 11th, 2012, 11:09 AM
Hi everyone. I'm fairly new, mediocre at best and I have a joining problem. I currently have 2 queries right now.
JOIN dbo.tbl_site ON dbo.tbl_site.site_ID = dbo.tbl_rate.rate_site_IDref
JOIN dbo.tbl_rank ON dbo.tbl_rank.rank_ID = dbo.tbl_rate.rate_rank_IDref
WHERE dbo.tbl_rate.rate_site_IDref = #FORM.site_ID#
FROM dbo.tbl_LEM, dbo.tbl_employee, dbo.tbl_site, dbo.tbl_rank
WHERE dbo.tbl_employee.employee_ID = dbo.tbl_LEM.employee_IDREF
AND dbo.tbl_LEM.site_IDREF = dbo.tbl_site.site_ID
AND dbo.tbl_rank.rank_ID = dbo.tbl_employee.employee_rankREF
AND dbo.tbl_LEM.LEM_date BETWEEN '#FORM.LEM_datefrom#' and '#FORM.LEM_dateto#'
AND dbo.tbl_site.site_ID = #FORM.site_ID#
AND dbo.tbl_LEM.LEM_posted = 'yes'
ORDER BY dbo.tbl_LEM.LEM_date ASC
I want to somehow join these 2 queries mentioned above.
Here are the column names for all the tables I need to pull out.
rate_ID, rate_site_IDref, rate_rank_IDref, rate_dollar, rate_ot, rate_dt, rate_loa
LEM_ID, LEM_date, employee_IDREF, LEM_reg_hrs, LEM_ot_hrs, LEM_dt_hrs, LEM_tt_hrs, site_IDREF, LEM_LOA, LEM_Expenseinfo,LEM_workorder, LEM_posted, LEM_Expense, LEM_reg_rate, Lem_ot_rate, LEM_dt_rate, Lem_tt_rate, Lem_loa_rate
employee_ID, employee_name, employee_number, employee_rankREF
site_ID, site_Name, site_JobNumber
What I need to accomplish is: After navigating to a specific jobsite (#FORM.site_ID#) and specific date ( #FORM.LEM_date), the user chooses an employee from a drop down list and assigns hours (regular time, overtime, double time, etc) then the info gets posted to tbl_LEM. Which appears in an ongoing list on the same page. Each Iteration of rs_getLEMS needs to somehow have the rates from rs_getrates ( rate_dollar, rate_ot, rate_dt, rate_loa) in hidden input feilds ( LEM_reg_rate, Lem_ot_rate, LEM_dt_rate, Lem_tt_rate, Lem_loa_rate) so the user can "post" and update tbl_lems.
Hopefully this makes it all clear...
February 11th, 2012, 04:49 PM
If there a foreign key between tbl_LEM and tbl_rate?
February 13th, 2012, 07:38 AM
No there is not.
tbl_rate is a link table for tbl_site and tbl_rank.
February 13th, 2012, 09:46 AM
If there's no way to join the tables I'm not sure what you actually want to do. How can you merge this all into one query if there is no way to relate the two tables?