Need to select first address in Access database

Question

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.

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

Use order by like this:

ORDER BY (CASE WHEN [DateFrom] IS NULL THEN 1 ELSE 0 END) DESC, 
         [DateFrom] DESC

I found the answer from this post. Please make sure to search first.

edited May 23 '17 at 10:34

Community

1
1

answered Jul 23 '12 at 09:40

Ahmad

12,336
6
48
88

2

SQL is a generic term. CASE is not available in MS Access SQL. – Fionnuala Jul 23 '12 at 09:58

score 0 · Answer 2 · answered Jul 23 '12 at 09:56

How about:

SELECT Adr.ID, Adr.EmployeeID, Adr.Postcode, Adr.DateFrom
FROM Adr
WHERE (((Adr.ID) In 
  (SELECT Top 1 ID 
   FROM adr b 
   WHERE b.EmployeeID=Adr.EmployeeID 
   ORDER BY DateFrom DESC )));

The above is built using the query design wondow, so half the parentheses are unnecessary, however, if you are using the query design window, you may as well leave them.

score 0 · Answer 3 · answered Jul 24 '12 at 09:38

0

Could you simply update the null values to be a set date like 1900-01-01?

answered Jul 24 '12 at 09:38

bendataclear

3,802
3
32
51

Need to select first address in Access database

3 Answers3