multiple dates - show the most recent

[Carrie]

Hi,
We have a database that tracks a number of different actions that each have
a due date and a reminder date. The actions are:
1. Declaration Due
2. 90 Day Declaration Due
3. Land Sale
4. Reacquire
5. Transfer
6. Convert
7. Abandon
Each record will begin with 1 or 2 and will then go through any - or all -
of 3 through 7. For the purposes of building a report to allow us to quickly
review the status' I would like to develop a query that will only select the
most recent due date and corresponding reminder date. I was thinking I might
need to use an Iif statement? Can anyone help get me started? Thanks a lot!

 

[Duane Hookom]

Did you have a table structure you could share? How about some sample
records?

We don't know if you have set up a spread-sheet like table or have a
properly normalized application where each date and action create their own
record.

 

[Carrie]

Hi, this is a very basic database. All we use it for is to track the dates
and decisions about what to do with our wells where rights have expired.
There is a detail table that houses information about the location (of a
well) and then the rest of the data is in the Main Table. Here's the fields
(minus some irrelevant ones like comments)

Record_ID
Status (active/closed)
Current_Action (listed in my question)
Declaration_Notice_Rcvd (Date)
Response_Due (Date)
Declaration_Reminder (Date)
RTP_90Day_Rcvd (Date)
90Day_Due (Date)
90Day_Reminder (Date)
Post_Land_Sale (Yes/No)
Land_Sale_Date (Date)
Land_Sale_Reminder (Date)
Reacquire (Yes/No)
Reacq_Ext_Granted (Date)
Reacq_Ext_Reminder (Date)
Transfer (Yes/No)
Transfer_By (Date)
Transfer_Reminder (Date)
Convert_Wellbore (Yes/No)
Abandon (Yes/No)
Abandon_By (Date)
Abandon_Reminder (Date)

So basically we recieve either a Declaration Notice or a 90 Day Declaration
Notice that our rights have expired. We then have to decide whether we want
to Post to the Land Sale, Reacquire, Transfer, Convert or Abandon. The govt.
then gives us a due date for completing that action (all the dates that
aren't reminders) and the reminder dates calculate 7 days prior to the due
date so that we can send out a reminder to the people responsible.

The problem is that we may perform only one action, or we may perform any
combination of the actions for each well. For example we may decide to post
to the land sale but then another company gets the bid. Then we may decide
to simply abandon the well or we may decide to try and transfer it to them.
If we try to transfer and the company says no, we will still have to abandon
the well.

Basically, we are just responsible for tracking the process to make sure our
company doesn't miss any due dates. So what we want is a report that will
list the well info (from the detail table) and the current action (if the
status is active) and whatever due date for the record is the most recent
(and corresponding reminder date).

We need to keep all the dates in the system but I have thought that the
easiest solution may just be to create two new fields (Current_Date and
Current_Remind) and have the staff type them in manually......

 

[Duane Hookom]

I would probably not work any further on this until it was normalized as I
suggested/asked in my previous message. If you can't normalize then try come
back here for alternatives. You might be able to use a union query to
normalize your table.