March 24th, 2013, 12:24 PM
INNER JOIN returns duplicate values
I have a report based on a query in access whereby a user selects an event via a combo box and the event id is used to search all he personnel and hired personnel associated to that event.
My query is as follows:
SELECT DISTINCT Tbl_Event.Event_id, Tbl_Event.Event_Name, Tbl_Event.Event_Location, Tbl_Event.Event_Date, Tbl_Personnel.LName, Tbl_Personnel.FName, Tbl_Personnel.Post, Tbl_Personnel.Rate, Tbl_Hired_Personnel.LName, Tbl_Hired_Personnel.Mobile_No, Tbl_Hired_Personnel.Rate
FROM Tbl_Personnel INNER JOIN (Tbl_Hired_Personnel INNER JOIN ((Tbl_Event INNER JOIN Tbl_Event_Personnel ON Tbl_Event.Event_id = Tbl_Event_Personnel.Event_id) INNER JOIN Tbl_Hired_Personnel_Event ON Tbl_Event.Event_id = Tbl_Hired_Personnel_Event.Event_id) ON Tbl_Hired_Personnel.H_Personnel_id = Tbl_Hired_Personnel_Event.H_Personnel_id) ON Tbl_Personnel.Personnel_id = Tbl_Event_Personnel.Personnel_id
WHERE (((Tbl_Event.Event_id) Like [Forms]![Event Staff Schedule Form]![cboName]));
The problem is suppose i have 2 personnel and 1 hired personnel associated to it, when i generate the report it displays all the right records but the same hired personnel twice, as if to compensate for the same number of records as personnel
Any help will be the most welcomed
Tags for this Thread
Click Here to Expand Forum to Full Width
This is a CodeGuru survey question.