Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Angry Unanswered: Simple Database design question

    If I have 3 tables...table 1 is employee with employee fields including salary...table 2 is classes (an employee can have 0-many classes...table 3 is events (an employee can have 0-many events). Each table is joined by the foreign key EmpID.

    question is...why does it duplicate the salary every time it finds a row in events table or classes table.

    For an employee with a $10,000 salary, 3 classes, and 2 events...access adds the salaries together to make a $ 60,000 salary...help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL statement of the query involved.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    4

    SQL Statement...Thanks

    SELECT Employees.FirstName, Employees.LastName, Salary.Salary
    FROM ((Employees INNER JOIN Events ON Employees.[Emp ID] = Events.[Emp ID]) INNER JOIN Salary ON Employees.[Emp ID] = Salary.[Emp ID]) INNER JOIN [Students And Classes] ON Employees.[Emp ID] = [Students And Classes].[Emp ID];


    Data Sheet View:

    First Name Last Name Salary
    Joe Salvo 10000
    Joe Salvo 10000
    Joe Salvo 10000
    Joe Salvo 10000
    Joe Salvo 10000
    Joe Salvo 10000
    James Alto 15000
    James Alto 15000
    Sally Weathers 22000
    Kelly McGee 12000
    Joey Vanto 25000

    It joins every table for Salvo and Alto who have more than 1 class or event and replcates the salary for each occurrence...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This seems normal to me and you're getting the expected results form the query. May be you don't use the proper query, though. What results were you expecting?
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    I was expecting Salvo's salary to show as 10,000...not 6 times 10,000 because there were 6 rows joined...I wanted to see his salary once BUT his events and classes multiple times

  6. #6
    Join Date
    Sep 2011
    Posts
    19
    Adding the word "Distinct" will eliminate the similar values on your query and only show you the stuff you want.

    Previously, the SQL you had was displaying everything properly, but I understand you don't want multiple values. Here is the SQL below with the updated information. Hope this helps!

    Code:
    SELECT DISTINCT Employees.FirstName, Employees.LastName, Salary.Salary
    FROM ((Employees INNER JOIN Events ON Employees.[Emp ID] = Events.[Emp ID]) INNER JOIN Salary ON Employees.[Emp ID] = Salary.[Emp ID]) INNER JOIN [Students And Classes] ON Employees.[Emp ID] = [Students And Classes].[Emp ID];

  7. #7
    Join Date
    Sep 2011
    Posts
    4
    Thanks so much Ari Potter...the DISTINCT made it work!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •