468,747 Members | 1,664 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,747 developers. It's quick & easy.

Get Most recent customer review

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.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?

Jul 3 '08 #1
3 1489
Stewart Ross
2,545 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Banks Databse].[Client Number], Max([Banks Databse].[Last Updated]) AS [Last Review]
  2. FROM [Banks Databse]
  3. GROUP BY [Banks Databse].[Client Number];
Then join qryLastDate to your [Banks Databse] table in the following query to retrieve the FinRevID value corresponding:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Banks Databse].[Client Number], [Banks Databse].[Last Updated], [Banks Databse].FinRevID, DateDiff("d",[Last Updated],Date()) AS Days
  2. FROM [Banks Databse] INNER JOIN qryLastDate ON ([Banks Databse].[Last Updated] = qryLastDate.[Last Review]) AND ([Banks Databse].[Client Number] = qryLastDate.[Client Number])
  3. WHERE (((DateDiff("d",[Last Updated],Date()))<400));
Test data:
Expand|Select|Wrap|Line Numbers
  1. Client Number Last Updated FinRevID
  2. 101 ..........06/02/2007 .....1
  3. 101 ..........06/02/2008 .....2
  4. 101 ..........06/06/2008 .....3
  5. 102 ..........03/06/2006 .....4
  6. 102 ..........03/09/2007 .....5
  7. 102 ..........03/05/2008 .....6
  8. 103 ..........01/05/2006 .....7
  9. 103 ..........01/08/2007 .....8
  10. 103 ..........01/09/2007 .....9
  11. 103 ..........01/05/2008 ....10
  12. 101 ..........08/04/2008 ....11
  13. 102 ..........02/03/2008 ....12
  14. 103 ..........01/04/2008 ....13
Expand|Select|Wrap|Line Numbers
  1. Client Number Last Review
  2. 101 ..........06/06/2008
  3. 102 ..........03/05/2008
  4. 103 ..........01/05/2008
Final query result:
Expand|Select|Wrap|Line Numbers
  1. Client Number Last Updated FinRevID Days
  2. 101 ..........06/06/2008 .....3 .....27
  3. 102 ..........03/05/2008 .....6 .....61
  4. 103 ..........01/05/2008 ....10 .....63
Jul 3 '08 #2
Genius Stewart

Thanks a million - that has worked perfectly.

I owe you a pint if are ever in Dublin!

Jul 3 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Glad to be of help, Anthony. Imagining that pint... Slainte!


..I owe you a pint if are ever in Dublin!
Jul 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Arvie | last post: by
3 posts views Thread by Tim Morrison | last post: by
1 post views Thread by Herman Beeksma | last post: by
14 posts views Thread by At_sea_with_C | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.