LAST Versus MAX 1

[MelissaKT]

Hello! I've been attempting to move my Access Database into SQL Server and use a ADP front end. I'm a newbie to Access Data Projects. Some of my .mdb queries include the LAST aggregate function. I had ran my query using the MAX function but I got a different answer than I did by using LAST. What can I do to make this work? My query looks like:

SELECT [tblDeedHistory].[PropertyNumber],
Last([tblDeedHistory].[DeedBook]) AS [LastOfDeedBook],
Last([tblDeedHistory].[PageNo]) AS [LastOfPageNo],
Last([tblDeedHistory].[PlatBook]) AS [LastOfPlatBook],
Last([tblDeedHistory].[PlatPage]) AS [LastOfPlatPage]
FROM [tblDeedHistory]
GROUP BY [tblDeedHistory].[PropertyNumber]
ORDER BY [tblDeedHistory].[PropertyNumber],
Last([tblDeedHistory].[DeedBook]),
Last([tblDeedHistory].[PageNo]);

 

[cmmrfrds]

A typical way to do this is to identify the last record by something like a sequence number, date, etc...., and make this a derived query ( a temporary table in the from clause of the query). Then join to this table.

SELECT A.PropertyNumber,
A.DeedBook,
A.PageNo,
A.PlatBook,
A.PlatPage,
A.yourdate
FROM tblDeedHistory as A
INNER JOIN
( SELECT tblDeedHistory.PropertyNumber,
max(tblDeedHistory.yourdate) as yourdate
FROM tblDeedHistory
GROUP BY tblDeedHistory.PropertyNumber
) as inview
ON inview.PropertyNumber = A.PropertyNumber
and inview.yourdate = A.yourdate
ORDER BY A.PropertyNumber,
A.DeedBook,
A.PageNo;

 

[MelissaKT]

Thanks a bunch! I really appreciate your help!! There is a deed date included in the table however, the users haven't always entered it. I didn't realize that there would be such a drastic difference between .mdb and .adp. It's been really hard trying to figure it all out!

 

[ESquared]

The Last() and First() of MDBs work based on the sort order of the entire query.

The Max() and Min() functions of MDBs and ADPs work on the 'sort order' of only the column specified in the expression.

To duplicate the Last() and First() functionality you have to craft an expression that sorts in the order of the main query but can be deconstructed to pull out the part you want.

So for example if your MDB query has First(OrderID) and ORDER BY LastName, your ADP query might look something like:

SELECT ParseName(Min(Replace(LastName, '.', ',') + '.' + Convert(varchar(10), OrderID)), 2)

Replace makes sure the LastName field has no periods in it.
Then ParseName simply returns the second part of the expression, everything after the period (a cheater's way to easily get part of a string out, it's normally used for server.database.owner.table expressions but often abused, especially for IP addresses).

I haven't actually tried this, I had the brainstorm as I read this message. Let us know if it works for you!