"Totals" Query: show last non-blank string

I have a totals query (one where I clicked the totals button, and it has "group by" columns) in Access 2007. Most of the columns are fine... group by columns, max columns, min columns, etc. For some of them though, I want to extract only the last non-blank (not "" or null) value of a string column.

Here's a sample of what my SQL looks like:

SELECT  Min(Duplicates.AttendedODBefore) AS AttendedODBefore,
        Min(Duplicates.ContactByPost) As ContactByPost,
        Last(Duplicates.PlannedStart) As PlannedStart,
        Min(Duplicates.AccessibilityRequirements) AS AccessibilityRequirements,
        Last(Duplicates.UcasNumber) As UcasNumber
--      ^^^^
FROM    DuplicateStudents As Duplicates
GROUP BY
        Duplicates.ID

The expression highlighted is the one I want changing to the last non-blank field. Is there an Access-specific or plain SQL expression which will do this?

Edit: Turns out that Min() and Max() work on string values and ignores null values, taking the first and last values alphabetically. It's not perfect, because it doesn't guarantee that the value selected is the last one, but it's better than just a load of nulls which is what using 开发者_StackOverflow中文版Last() might give.

Access seems resistant to the idea of returning the last non-null value in a query using GROUP BY. Even if your FROM clause were modified to be something like

FROM    (
    SELECT allfieldsyouneed 
    FROM DuplicateStudents 
    ORDER BY PlannedStart
) AS SortedDuplicates

and the rest of your query were modified to use SortedDuplicates instead, Access doesn't seem to return the last value based on the order you specify. I tested on a table with exactly one blank row and specific orders that I could verify (both an auto-increment field and a value that I checked by using Min and Max), and Access chose to return some other value as Last.

Based on your comment it seems like your definition of "Last" is: the most recently added record, based on an auto-incrementing ID. As such, some form of the following should work (it uses a subquery to return the most recent non-null UcasNumber):

SELECT  Min(Duplicates.AttendedODBefore) AS AttendedODBefore,
        Min(Duplicates.ContactByPost) As ContactByPost,
        Last(Duplicates.PlannedStart) As PlannedStart,
        Min(Duplicates.AccessibilityRequirements) AS AccessibilityRequirements,
        (SELECT TOP 1 D.UcasNumber FROM Duplicates AS D
         WHERE D.UcasNumber Is Not Null
         ORDER BY D.ID DESC) As UcasNumber
FROM    DuplicateStudents As Duplicates