Query to get the most recent customer reviews

[AnthonyT]

Hi All

I have a major problem with an access query and I am near the end of my tether!

I have taken over a project with a badly built access database and as resources are not available to start over I have to make do and cobble together a fix.

DB is design to record customer [Identified by Cust_ID, which is a random number generated by a different system] reviews [Identified by Review_ID, which is an Access auto number with older reviews having a lower number and more recent reviews having a higher number].

It was built as 1 table with 1 line per review. So as you can imagine you can have several reviews for the same customer over time.

What I need to do is isolate the most resent review for each customer where the day count between today and the review is less the 400.

So iahve built one query to get the most Review_ID of the most recent review and the another query that gets all the relevent info for those IDs

However there seems to be no pattern to the results I get. Sometimes I get the most recent review other times I get the oldest and othertimes, when there are three reviews, I get the middle one!

Here is what I have tried:

SELECT bd1.[Client Number], Min(DateDiff("d",bd1.[Last Updated],Date())) AS Expr1, Last(bd1.FinRevID) AS LastOfFinRevID
FROM [Banks Databse] AS bd1
GROUP BY bd1.[Client Number]
HAVING (((Min(DateDiff("d",[bd1].[Last Updated],Date())))<400))
ORDER BY bd1.[Client Number], Last(bd1.FinRevID);


Any ideas?

Thanks

 

[neileg]

Last (and First) have issues. That's because data in a table has no implicit order. Even when you apply a sort, this only sorts the display of the data, not the data itself. So what appears to be the last record may not be.

Use Max (or Min) which don't have this problem.

 

[Brianwarnock]

The other problem is that Last First Min Max are field operators and you will get the results for the group not a particular record.

Brian