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!!

 

[dk]

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

Hi,

The simplest way is to create a query as per normal. Then click the totals button. In the Total row choose Max for your date field and group by for the others.

 

[kart_deBlonch]

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

I have been testing this and I find that it will only accomplish what I need if I have records that are identical in all respects except the field that is being “maxed”. Otherwise it just returns all records. I must be missing something but what?

 

[jobb]

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

probably not the simplest way, but you could group by a primary key, then the account, then the max updated date in one query (just those fields!)

then link up that query to another query, by it's primary key and the additional fields.

I don't know if you have a primary key or ID Number, but you will need one for this solution.

 

[kart_deBlonch]

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

Jobb:

Your suggestion sounds like a good one; however, I am a little too busy right now to do any testing. Maybe later today. Yes, I will have a Primay Key (A "ReviewID" No Duplicates) in my main table and an Account ID that will be duplicated in multiple records. I am just wondering though: if this isn't the simplest solution, what is? Thanks guys for the ideas. You are certainly helping me out.

 

[jobb]

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

I just realized that I didn't word my response very well.. something I'll have to get used to

"then link up that query to the original table in another query. Join the primary key and then include the additional fields from the table."

it probably made sense anyway?

I'm self-taught in Access, and I never really know if something is the simplest solution. Since you only need 2 queries for this though, maybe it is the simplest?

 

[kart_deBlonch]

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

Jobb:

Thanks for the additional information. I will need it when I test this solution out. It sounds like an interesting system. I just wish I had the time to test it. Right now I am scrubbing-normalizing data that I have in about 100,000 links to cells in 700 excel files. The "data entry" that has been used in this shop has not been "database friendly". Maybe you could come up with a simple little table that replicates my situation and then test your idea with it? You might be just as curious as me to see if your idea will work correctly. Thanks again...

 

[jobb]

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

wait, scratch that, I messed up. If you group by the ReferralID #, you will have the same problem of including records you don't want.

Unless you can make a primary key out of the Account Name/time last updated?

if someone else knows another way, I'd be interested to know as well.

 

[kart_deBlonch]

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

Jobb:

So what exactly is wrong with what you were suggesting? At what point did it break down? I am going to have to try this now because I have to see this for myself. What I have been working on can wait...

 

[kart_deBlonch]

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

Oh, I see what you mean. It won't discriminate using the date and the AccountID.