How to write a query that selects the most recently updated

[kart_deBlonch]

I have tried to go through the archives here to find help with my question but I have not had any luck. So here goes: I am about to create an Access database that will do two basic things: [1] track the work done by my account review staff, and [2] provide statistics about accounts based on reviews that the account review staff completes. I will be starting with about 700 records. This is the number of reviews our staff has completed in the past 2 calendar years. Many of these reviews have been completed on the same accounts, but each review does have its own review date. Some accounts may have two to three reviews completed per year. The information contained in my 700 records will go into one "main" table, and I will probably have a couple of other tables that will have one-to-many relationships to fields in my main table. While I have thought through just about everything I want the database to do, there is one thing that I will need the database to do that I don't know much about. Which is: how do I write a query that will select only the most recently completed review for each account review from my "main" table? Thanks!!

 

[kart_deBlonch]

Re: How to write a query that selects the most recently upda

Parra:

So DESC gave me the minimun date? Okay, I understand that but how do we get it to return a date for each accountID in the table? I should be getting back lots of dates not just one. That part is the real trick. Thank you.....

 

[kart_deBlonch]

Re: How to write a query that selects the most recently upda

Guys here is some sample code that takes the MaxOfDate and GroupBy on AccountID (Query1) to accomplish what we have been discussing in a second query:

SELECT T_Someones_Info.*
FROM Query1 INNER JOIN T_Someones_Info ON (Query1.MaxOfLast_Updated_Date = T_Someones_Info.Last_Updated_Date) AND (Query1.Account_ID = T_Someones_Info.Account_ID);

Let me know if you would like the sample db that this came from and I will post it.

 

[Parra]

Re: How to write a query that selects the most recently upda

OK Kart lets see if this other suggestion works.

Create a regular query and I'll make it simple with 2 fields.
Last Updated and Account Number.
Now use the Icon group, I think you know which one it is, the thing that looks like the letter E. Ok, on the Last Updated, changed "Grouped" to "Last". This will give for each account, the most recent date.

I think this was what you were looking for. Then just add other fields as needed.

Did this help?

Parra

 

[kart_deBlonch]

Re: How to write a query that selects the most recently upda

Parra:

Yes you are corrct but the issue that one is confronted with is how access will group the other fields. I think you will find that if you try to add more fields to the query as you define it, you will get more records based on the number of unique values you put into the other fields and then a selection of, say, three records starts to multiply into lots of "records" that have no resemblence to the original info. If you think I am wrong with respect to this, I would love to see an example that you have that shows otherwise. Not that I am saying you can't do it, but...What query2 does, in the sql that I posted this morning, is to "attach" the additional fields to the records found in query1 without "GroupingBy" those other values. in this way, the records are selected in a discreet, review by review, selection.