multiple table queries

[Sam11420]

PLEASE HELP ITS URGENT
i created a query using 5 different tables all tables are linked to table1
1 person can have more than 1 qualification and more than 1 different type of exp and more that 1 industry exp and only 1 medical exp date
table1
surname
name

table2 -
qualification
expiry date

table3
type of exp

table4
industry exp
yrs of exp

when i run the query all the information show as it should BUT the problem is information is duplicated like:
Smith Joe PT1 NDT exp 4 yrs FAC 16/01/2016


Smith Joe MT2 Insp exp 1 yr DB 16/01/2016
Smith Joe PT1 NDT exp 4 yrs LB 16/01/2016

If say for instance the employee has 4 qualifications everything is repeated, how do I get my query to look like this
Smith Joe PT1 NDT exp 4yrs FAC 16/01/2015
MT2 Insp exp 1yr DB
LB

Basically remove repeats

[alansidman]

Post your SQL statement so that we can see exactly how you are joining the tables.

[orange]

Can you describe in 3 or 4 lines, simple English, WHAT the database is about?
Then post a jpg of your tables and relationships, and, as Alan asked, show us the SQL.

My guess is that there could be a structure problem.

[sstiebinger]

Also, you say that each person may have "More than", but is there a minimum? Will they always have at least 1 entry in each table?

[Sam11420]

As requested please find below SQL Statement


SELECT [Surname] & ", " & [Name] AS Employee, tbl_EmplQualification.Qualification, tbl_yrsofexp.[Type of Exp], tbl_yrsofexp.[Years of Exp], tbl_Employee.[Contact Number 1], tbl_Employee.Status, tbl_Employee.[Job Description], tbl_TypeofExp.[Type of Exp], tbl_TypeofExp.Exp
FROM ((tbl_Employee LEFT JOIN tbl_EmplQualification ON tbl_Employee.link = tbl_EmplQualification.link) LEFT JOIN tbl_TypeofExp ON tbl_Employee.link = tbl_TypeofExp.link) LEFT JOIN tbl_yrsofexp ON tbl_Employee.link = tbl_yrsofexp.link
WHERE (((tbl_Employee.Status)=[Forms]![frm_searchJobDesctoStatus]![Status]) AND ((tbl_Employee.[Job Description])=[Forms]![frm_searchJobDesctoStatus]![JobDesc]))
ORDER BY [Surname] & ", " & [Name];


I have exported the query, please see attached spreadsheet.

I have also attached a spreadsheet of how I would like the query to run.

I have also attached a screenshot of the relationships.

please guys I am a newbie at this.

[ssanfu]

re: https://www.accessforums.net/queries...che-55634.html
As Joe said (post #11), there are no duplicate records. There are, however, fields in the result set that are the same (for example the "Employee" field).



To have the results of the query look like the example spreadsheet, you could try creating a report, grouping on "Employee" (full name). Or have VBA code to write the data to a temp table, then display using a form/report.


About your tables.... Be aware that "Name" is a reserved word in Access and shouldn't be used for object names.
Should only use letters, numbers and possibly the underscore in object names. Do not use SPACES, punctuation or special characters.

IMO, "Link" is a poor name for a field.
"tbl_miss" has repeating fields - a sure sign of design error.