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

[Stewart Ross Inverness]

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

qryLastDate:

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

-Stewart

[AnthonyT]

Genius Stewart

Thanks a million - that has worked perfectly.

I owe you a pint if are ever in Dublin!

Rgds

[Stewart Ross Inverness]

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

-Stewart

Originally Posted by AnthonyT

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