Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Access 2003, query table /w duplicates & print only earliest

    Hello,

    I have 2 tables. 1 with person information (Name, ID#, hire date) and the other with salary info (ID#, salary, date of raise). This second table has many records for each person (each date they got a raise)

    I am trying to build a query that will print the name, ID, hire date, date of FIRST raise and the time in months between the 2 dates.

    I am having no luck using the "totals" first, last, min ect. Can anyone advise? Thanks very much.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,329
    Thanks
    1
    Thanked 13 Times in 13 Posts
    Quote Originally Posted by winner-88 View Post
    Hello,

    I have 2 tables. 1 with person information (Name, ID#, hire date) and the other with salary info (ID#, salary, date of raise). This second table has many records for each person (each date they got a raise)

    I am trying to build a query that will print the name, ID, hire date, date of FIRST raise and the time in months between the 2 dates.

    I am having no luck using the "totals" first, last, min ect. Can anyone advise? Thanks very much.
    SELECT PersonName, ID, HireDate, MinOfDateOfRaise, MinOfDateOfRaise - HireDate AS DaysToRaise FROM tblPerson INNER JOIN (Select ID, Min(DateOfRaise) as MinOfDateOfRaise FROM tblRaises GROUP BY ID) AS SQ ON tblPerson.ID = sq.ID
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    winner-88 (2011-06-10)

  4. #3
    New Lounger
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the quick reply Mark, I appreciate it.

    However, I have been fiddling with the query you gave me for a while trying to get it to work, but I cant seem to get it. I put all my table names into the code and when I run the query it asks me for the
    Employee ID, Compensation Date, and Hire Date if i type 1 for both then it displays all the employee names without duplicates but lists "hire date" and "compensation date" as whatever number i entered in the beginning

    I have 2 tables for this query. Table 1 is named Employees and table 2 is named CompensationHistory

    Here is my code, any idea the problem? Thanks

    SELECT FirstName, EmployeeID, [Hire Date], [MinOfCompensation Date], [MinOfCompensation Date] - [Hire Date] AS DaysToRaise
    FROM Employees INNER JOIN (Select EmployeeID, Min([Compensation Date]) as [MinOfCompensation Date] FROM CompensationHistory GROUP BY EmployeeID) AS SQ ON Employees.employeeID = sq.EmployeeID

    EDIT:

    I added [] to the columns with names that contained spaces (sorry, new to SQL)
    This query saves without errors but when run
    Last edited by winner-88; 2011-06-10 at 16:32.

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    In the Employees table have you defined Hire Date or is it HireDate?
    Can you post a compacted/zipped database with the tables and query that are a problem here.

Tags for this Thread

Posting Permissions

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