duplicates


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: duplicates

  1. #1
    Join Date
    Jan 2006
    Posts
    17

    duplicates

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Please post the query you're using as the data source for your report.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jan 2006
    Posts
    17
    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];

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    What happens when you remove DISTINCT?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Jan 2006
    Posts
    17
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Jan 2006
    Posts
    17
    Thank you so much for your time and help!

    Mike

  8. #8
    Join Date
    Feb 2006
    Posts
    55
    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.
    David Wiseman
    MCSE (2000/2003), MCSA (2003), MCDBA
    www.wisesoft.co.uk


  9. #9
    Join Date
    Jan 2006
    Posts
    17
    You have shed some light. I really appreciate your time and Information. This is very helpful.

    Thanks,
    Mike

Similar Threads

  1. Replies: 1
    Last Post: 10-25-2005, 08:59 AM
  2. Deleteing duplicates integers from a vector!!!
    By Paul Statham in forum Java
    Replies: 0
    Last Post: 11-04-2001, 06:23 AM
  3. SQL 2000 Delete Duplicates
    By Mike Kopa in forum Database
    Replies: 3
    Last Post: 12-31-2000, 10:03 AM
  4. Duplicates in SQL
    By Claire in forum Database
    Replies: 2
    Last Post: 05-08-2000, 07:42 PM
  5. Replies: 1
    Last Post: 03-31-2000, 09:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center