Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Select current address from a list

    I'm using Access in Office 10 in a mixed Windows 7 / Windows XP environment.

    I need to be able to select the current address for employees from a list. The problem I have is that the address "datefrom" could be past, future or null.

    Removing future is obviously easy in the criteria, i.e. "WHERE datefrom <=date()"

    The problem I have is that in the initial import of address data, most addresses did not have this information, and so the field is null. An example of the data is below: (date format is dd/mm/yyyy)

    ID EmployeeID Postcode DateFrom
    1 1 AB12 3CD [null]
    2 2 GH12 5RF [null]
    3 1 CD34 5EF 10/03/2012
    4 3 HA25 3PO [null]
    5 3 HA4 7RT 04/06/2012]
    6 3 DB43 5YU 12/11/2011]

    My desired output would be: (order of employees not important)

    ID EmployeeID Postcode DateFrom
    2 2 GH12 5RF [null]
    3 1 CD34 5EF 10/03/2012
    5 3 HA4 7RT 04/06/2012

    I've tried sorting by DateFrom DESC which does order the list as below:

    ID EmployeeID Postcode DateFrom
    3 1 CD34 5EF 10/03/2012
    1 1 AB12 3CD [null]
    2 2 GH12 5RF [null]
    5 3 HA4 7RT 04/06/2012
    6 3 DB43 5YU 12/11/2011
    4 3 HA25 3PO [null]

    So if I could then just take the first result for each employee I'd be fine. However I've tried (and failed) to do SQL including things like DISTINCT, first() and GROUP BY, but don't seem to be able to get anywhere.

    I probably just can't see the easy obvious answer, so any help would be very much appreciated.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try this: make a Select totals query displaying the employeeID and Max(DateFrom). Save it, say as qryAddrSub. In the next query, display the EmployeeID, PostCode, and DateFrom from the original table, include the query qryAddrSub as another recordset in the query, and Inner Join the two Employee ID fields together, and also the main table's DateFrom and the query's MaxDateFrom together. That should give you what you need.

    There are other ways to skin the cat; this is the way I'd do it.

    Sam

Posting Permissions

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