Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Question Unanswered: not sure how to do this

    I have a list of employee data that has multiple records from one employee. The data varies based on two date fields (hire, termination). I need to take from the list, one record for each employee with their earliest hire date and latest termination date. Is it possible to write this in an SQL query? I've never used vba in access so I wouldnt know how to start. I do know ADO for ASP, is it similar?

    How should i do this?

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink Hi there

    what you can do is the following

    [Design view]

    Take yor table 2 times left join them with the name

    then you take from the first table(original) the name
    from the second table the date's
    in the toolbar select the totals symbol the like "E" sign

    Group by the name
    Min date 1
    Max date 2

    Run
    Jump in the air (if it works :-)
    Cry and smash the computer :-( if it does'nt)


    [SQL view]

    SELECT Table.Name, Min(Table_1.date1) AS MinOfdate1, Max(Table_1.date2) AS MaxOfdate2

    FROM Table

    LEFT JOIN Table AS Table_1 ON Table.Name = Table_1.Name
    GROUP BY Table.Name;

  3. #3
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: not sure how to do this

    I don't know what your table or field names are, but
    this sql would seem to work:

    select name, id, <other fields>, min(hire) as first_hire, max(term) as last_term
    from emp_list
    group by name, id, <other fields>

    but it will give you one record per employee only if all the fields except hire and term are identical in their values.

    if you want to create a new table with these values, then you could use

    select name, id, <other fields>, min(hire) as first_hire, max(term) as last_term into new_table
    from emp_list
    group by name, id, <other fields>

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    If all this data is an access (which I assume it is) and it sounds like it is in one table(list), The it should be possible with One SQL statement similar to the following:

    SELECT Employees.EmpID, First(Employees.EmpName) AS EmpName, Min(Employees.HireDate) AS HireDate, Max(Employees.FireDate) AS FireDate
    FROM Employees
    GROUP BY Employees.EmpID


    This assumes (in this example) the the employeeID is always the same and is correct in the table. Pulls the first entry of EmployeeName out and Find the earliest hiredate and the Last FireDate

    You will need to modify it for your DB

    S-

  5. #5
    Join Date
    Nov 2003
    Posts
    6
    You guys are awesome. I completely forgot about the min/max functions!

    wait a minute...
    Last edited by jvargas; 11-25-03 at 13:38.

Posting Permissions

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