duplicates

[garzamk]

Can someone tell me why I'm getting duplicate Employee names on my MS Access report?

The Employee table does not have duplicates and the EmployeeName is the Primary Key.

The duplicates show up when I create a query with more than one table.

Thank you very much.

Mike

[Phil Weber]

Please post the query you're using as the data source for your report.

[garzamk]

This is the SQL view. I noticed that the only employee duplicates are the Companies that have the same Job Classifications. I hope this helps. The table relationship is the Job classification in the JobClass and Employee table.

Thanks,

SELECT DISTINCT [Employees].[EmployeeName], [Employees].[SSNo], [Employees].[Comp], [Employees].[JobClass], [Employees].[PerDiem], [JobClassifications].[StraightRate], [JobClassifications].[OTRate], [JobClassifications].[PerDiemRate], [JobClassifications].[HolidayRate], [JobClassifications].[SundayRate]
FROM JobClassifications INNER JOIN Employees ON [JobClassifications].[Classification]=[Employees].[JobClass]
ORDER BY [Employees].[EmployeeName];

[Phil Weber]

What happens when you remove DISTINCT?

[garzamk]

Good Morning Phil,

I get more duplications if I take out the 'DISTINCT' Predicate.

For some reason if I have the Job Class of 'TECHNICIAN' or any other identicle Classification in multiple companies, I get duplications.

I have 809 employees in the 'EMPLOYEE' Table but when I run my employee list query which includes an inner join to Job Class in my 'JobClassification' Table, I get a total of 969 records.

The company name is the Primary Key in the JobClassification Table but I also have an Index for the Job Class, and the Employee Name is the primary key in the Employee table.

I'm not sure if this is causing the conflict with the Indexes.

I also have the Database split, the application is on the user desktop and the tables are on the server.

I really appreciate your help.

Thanks,
Mike

[Phil Weber]

Right, because DISTINCT says, "Give me every unique combination of Employee Name, Company, Job Classification, etc." If you have the same job classification in multiple companies, that's multiple unique combinations, so DISTINCT obediently returns them as separate records.

I don't have time at the moment, but I'll try to come up with an example later today that should work without duplicates.

[garzamk]

Thank you so much for your time and help!

Mike

[Wiseman82]

Is primary key not CompanyName & JobClass?

You should probably join on CompanyName and JobClass. e.g.
..
FROM JobClassifications INNER JOIN Employees
ON [JobClassifications].[Classification]=[Employees].[JobClass]
and [JobClassifications].[CompanyName]=[Employees].[CompanyName]
..

(Your column name for company name might be different)

In the JobClassifications table probably has entries like:

CompanyName Classification OTRATE .....
Company ABC EMPLOYEE 1.5
Company XYZ EMPLOYEE 2.0
Company ABC TECHNICIAN 1.0

If you join on Classification you will get duplicate rows because Classification is not unique.

[garzamk]

You have shed some light. I really appreciate your time and Information. This is very helpful.

Thanks,
Mike