Help with Query writing

[nyrob609]

HI,

I have a table that keeps record of applications with expiration dates for applicants. What I would like to do is query expired applications? but there are some multiple records per applications that have expired or are current. Which is ok?? We want to keep track of everything. How do I query just the records that are expired but don't have another current record. So, that my staff could make sure that we have an updated application for the applicants. Please help me.

For example

Applicant ID # Expired Date
1 9/30/2009
1 10/31/2009
2 11/30/2009
3 8/31/2009
3 5/31/2009
3 12/31/2009

 

[jzwp22]

What rule determines when an application is expired or not? It might be better to track the date the application was submitted. Let's say that all applications expire 90 days from the date they are submitted. You can check the date submitted versus today (using the datediff() function) to see which records have a difference of more than 90 days. If you want to limit the search to only the most recent submission by each applicant, a query with the DMax() function can help you do the initial filtering and then check only those records to see if the difference in dates is greater than 90 days (or whatever criteria you use).

 

[nyrob609]

Thanks, I think this work...I will group by Max Date and then I could pull expired applications.

 

[jzwp22]

Glad to hear that the DMAX() will work for you.

 

[jzwp22]

I wanted to apologize, I kept saying to use the DMax() function rather than an aggregate query using Max([Expired Date]). Sorry for any confusion this might have caused.