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

 

[jobb]

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

I read your email and it said you might have 2 or 3 reviews for the same account in a year. Well, as long as you don't have two reviews for the same account on the same date, you can create an index (or primary key) in table design based on review date and account.

Then you can do the Group By Account/ Max date - query

Then in your second query join the date and account in your table with the date and account in your first query, then include the additional fields.

 

[kart_deBlonch]

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

Jobb:

So this is just a matter of having two primary keys in the main table? One would be the account ID and the other would be the review date, correct? Interestingly enough, I have been wondering what the advantages of two primary keys in one table would be. Now, I guess I know. I am back to testing this. Thanks, Jobb

 

[kart_deBlonch]

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

I am having difficulty in creating these two primary keys because I need them both to be indexed with duplicates 'OK' and that would cause duplicate indexes or primary keys. How do I tell access that I promise not to have two account numbers with two review dates that are the same?

 

[jobb]

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

well, you can only have one "primary key". I would suggest you try this:

in the table's design view, just click on View>Indexes

Index Name___________Field Name___________Sort Order
1 Primary Key__________Referral ID___________ Ascending (?)
(when you highlight this one, check yes under primary key)
then next line:

2 Account&Date_________Account#_____________Ascending
(leave blank) __________Date _______________ Ascending

Then highlight Account&Date and check yes under Unique. You can make this or your referral # the primary key, as long as they both have no duplicates. I just put the sort orders for illustration, they are irrelevant to this example. If you try to save and it won't let you, check for duplicates with a duplicate values query. Good luck, and let me know if it works.

 

[jobb]

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

one more thing, Kart

If you have trouble with the example above, just highlight the three rows, referral ID/account#/date updated and click on the "golden key" on the tool bar, and it will make the three combined fields your primary key. That should work just as well.

 

[kart_deBlonch]

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

Jobb:

I really appreciate your help with this and I am sorry about not replying sooner but I will be a little busy most of today. I will try to get back to your latest suggestion later on and do some testing. One thing: After doing some testing last night and early this morning, I thing I will use an autonumber primary key for each review. How does this effect the situation we are working on? Type to ya later and thanks again...

 

[Parra]

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

I did'nt read the entire posting but I encoutered a similar problem and this is how I resolved it.

Created a query that gave me the most recent date.
SELECT DISTINCT TOP 1 Revenue.[1st Date]
FROM Revenue
ORDER BY Revenue.[1st Date] DESC;

Used the result of this query to create a new query to select on the records that had this date.

Simple and it did the job.

Parra

 

[kart_deBlonch]

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

Parra:

I have seen “Top 1” used before in an answer to a question posted on the board but in the situation I saw, it selected based on a criteria such as “Blue” or “Red”. Maybe you have seen the thread I am talking about. It looked like this system wouldn’t work for me since I can’t provide, in a practical way, all the unique criteria like “2002010019” as one accountID out of 700. Does your system work this way? I guess it doesn’t. Does it select for every distinct entry in a field in the Revenue table? I don’t see any other fields referenced in you statement. I will try your solution out and see if I can make it work. Thanks

 

[kart_deBlonch]

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

Parra:

I just tried:

SELECT DISTINCT TOP 1 [Account Review Main Table 002].[Review Date]
FROM [Account Review Main Table 002]
ORDER BY [Account Review Main Table 002].[Review Date] DESC;

And it retuned one date; The date returned happens to be the minimum date in the table. Seems like the sql should have a statement like "FOR EACH DISTINCT [Account Review Main Table 002].[Account Number]. Or maybe "ON EACH...."

What do you think I am doing wrong? Please help....

 

[Parra]

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

The only things that comes to mind is how you are sorting the data to get the date, ascending or decending.