Get Most recent customer review

[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 I have built one query to get the most Review_ID of the most recent review and the another query that gets all the relevant 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.FinRev ID) 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.FinRev ID);


Any ideas?

Thanks

[Stewart Ross]

Hi Anthony, and welcome to Bytes! Your logic does work - but only if an assumption is correct, which in this case it clearly isn't. The assumption is that the entries for each client will always be put into the table in date order. The last autonumber FinRevID for each client is otherwise not reflecting the last review date. It is this which is causing the apparent ordering problems for your query. The use of Last on the FinRevID is the problem here, as it is changing the grouping within the query if the entry order does not follow strict date order.

I have come to this conclusion after testing your query on sample data; your query works as expected when all entries follow in date order, but fails should a review be entered out of sequence.

To resolve this we need to break the problem down into two parts: find the most recent review date for each client, then retrieve the FinRevID that corresponds to that date (recognising that it is not necessarily the highest-value ID for that client).

Enter the query below and save it as qryLastDate:

SELECT [Banks Databse].[Client Number], Max([Banks Databse].[Last Updated]) AS [Last Review]
FROM [Banks Databse]
GROUP BY [Banks Databse].[Client Number];

Then join qryLastDate to your [Banks Databse] table in the following query to retrieve the FinRevID value corresponding:

SELECT [Banks Databse].[Client Number], [Banks Databse].[Last Updated], [Banks Databse].FinRevID, DateDiff("d",[Last Updated],Date()) AS Days
FROM [Banks Databse] INNER JOIN qryLastDate ON ([Banks Databse].[Last Updated] = qryLastDate.[Last Review]) AND ([Banks Databse].[Client Number] = qryLastDate.[Client Number])
WHERE (((DateDiff("d",[Last Updated],Date()))<400));

Test data:

Client Number Last Updated FinRevID
101 ..........06/02/2007 .....1
101 ..........06/02/2008 .....2
101 ..........06/06/2008 .....3
102 ..........03/06/2006 .....4
102 ..........03/09/2007 .....5
102 ..........03/05/2008 .....6
103 ..........01/05/2006 .....7
103 ..........01/08/2007 .....8
103 ..........01/09/2007 .....9
103 ..........01/05/2008 ....10
101 ..........08/04/2008 ....11
102 ..........02/03/2008 ....12
103 ..........01/04/2008 ....13

qryLastDate:

Client Number Last Review
101 ..........06/06/2008
102 ..........03/05/2008
103 ..........01/05/2008

Final query result:

Client Number Last Updated FinRevID Days
101 ..........06/06/2008 .....3 .....27
102 ..........03/05/2008 .....6 .....61
103 ..........01/05/2008 ....10 .....63

-Stewart

[AnthonyT]

Genius Stewart

Thanks a million - that has worked perfectly.

I owe you a pint if are ever in Dublin!

Rgds

[Stewart Ross]

Glad to be of help, Anthony. Imagining that pint... Slainte!

-Stewart

..I owe you a pint if are ever in Dublin!