How to remove duplication of data in subreport

[murtazadoc]

Hi...
Am developing a resume data retrieving software where data is stored in one parent table and 4 child tables as such:
Resume_table ----> Parent table
holds ----> Res_ID, Emp_ID, Lang_ID, Task_ID & some other static data

Employee_Table--->which is referenced directly to Resume_Table.Emp_ID
similarly ol other 3 tables are referenced to the main parent table

Storing and retrieving data is all working fine as long as I am retrieving 1 record for each child tables

But some times there might be couple of languages and only single records for other child tables.In dat case as the four child tables are ol in separate subreports whch are called onto the main report whch prints as cv gives redundancy in data i.e by giving duplication of ol records twice which were single records during entry, which i think follows the language table if it consist of 2 records.

am using a join query to call these records onto crystal report...

SELECT Distinct Education_Table.Edu_ID AS Education_Table_Edu_ID, Education_Table.Institution, Education_Table.Years_Edu, Education_Table.Edu_Description, Employment_Record.Emp_ID AS Employment_Record_Emp_ID, Employment_Record.From_Dt, Employment_Record.To_Dt, Employment_Record.Employer, Employment_Record.Pos_Held AS Employment_Record_Pos_Held, Language_Table.Lang_ID AS Language_Table_Lang_ID, Language_Table.Lang_Name, Language_Table.Lang_Speaking, Language_Table.Lang_Reading, Language_Table.Lang_Writing, Resume_Table.ResID, Resume_Table.Proposed_Position, Resume_Table.Firm_Name, Resume_Table.Resume_Name, Resume_Table.Dt_Birth, Resume_Table.Country_Work, Resume_Table.Profession, Resume_Table.Years_Group, Resume_Table.Nationality, Resume_Table.It_Skills, Resume_Table.Edu_ID AS Resume_Table_Edu_ID, Resume_Table.Lang_ID AS Resume_Table_Lang_ID, Resume_Table.Emp_ID AS Resume_Table_Emp_ID, Resume_Table.Details_Task_Assignd, Resume_Table.Assgnmt_ID AS Resume_Table_Assgnmt_ID, Resume_Table.Certification, Resume_Table.Staff_Member, Resume_Table.Authorised_Rep, Tasks_Assigned.Assgnmt_ID AS Tasks_Assigned_Assgnmt_ID, Tasks_Assigned.Name_Assgnmt, Tasks_Assigned.From_Assgnmt, Tasks_Assigned.To_Assgnmt, Tasks_Assigned.Location, Tasks_Assigned.Client, Tasks_Assigned.Proj_Features, Tasks_Assigned.Pos_Held AS Tasks_Assigned_Pos_Held, Tasks_Assigned.Act_Performed FROM Tasks_Assigned INNER JOIN (Language_Table INNER JOIN (Employment_Record INNER JOIN (Education_Table INNER JOIN Resume_Table ON Education_Table.[Edu_ID] = Resume_Table.[Edu_ID]) ON Employment_Record.[Emp_ID] = Resume_Table.[Emp_ID]) ON Language_Table.[Lang_ID] = Resume_Table.[Lang_ID]) ON Tasks_Assigned.[Assgnmt_ID] = Resume_Table.[Assgnmt_ID] WHERE (((Resume_Table.ResID)=" & Integer.Parse(txtResumeID.Text) & "));"
 

any help is really appreciated....
Thanx

[Aimee Bailey]

Avoid using nested joins, instead maybe try using a HAVING clause, or a sub-query that finds records appropriate for your needs. Also try and stay away from the DISTINCT keyword unless its absolutely nessisary.

Two cents brought to you by Aimee ;)

[murtazadoc]

wud ye mind elaborating it a bit more with context to my query...?
Cause hv been tring arnd lately bt havent found ny soln till yet...
have simplified d code

SELECT r.Proposed_Position, r.Firm_Name, r.Resume_Name, r.Dt_Birth, r.Country_Work, r.Profession, r.Years_Group, r.Nationality, r.It_Skills, r.Details_Task_Assignd, r.Staff_Member, r.Authorised_Rep, ed.Institution, ed.Years_Edu, ed.Edu_Description, em.From_Dt, em.To_Dt, em.Employer, em.Pos_Held, l.Lang_Name, l.Lang_Speaking, l.Lang_Reading, l.Lang_Writing, t.Name_Assgnmt, t.From_Assgnmt, t.To_Assgnmt, t.Location, t.Client, t.Proj_Features, t.Pos_Held, t.Act_Performed FROM (((Resume_Table AS r INNER JOIN Education_Table AS ed ON r.ResID = ed.Res_ID) INNER JOIN Employment_Record AS em ON r.ResID = em.Res_ID) INNER JOIN Language_Table AS l ON r.ResID = l.Res_ID) INNER JOIN Tasks_Assigned AS t ON r.ResID = t.Res_ID WHERE(((r.ResID) = " & txtResumeID.Text & ") or ((em.Res_ID) = " & txtResumeID.Text & ") or ((ed.Res_ID) = " & txtResumeID.Text & ") or ((l.Res_ID) = " & txtResumeID.Text & ") or ((t.Res_ID) = " & txtResumeID.Text & "))"
 
 

[MrMancunian]

Would you mind just using proper English instead of using abbreviations? Try using Aimee's two cents in your query and if you can't make it work, come back and post your new query.

Steven