If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
multiple dates - show the most recent
Hi,
We have a database that tracks a number of different actions that each have a due date and a reminder date. The actions a 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! |
#2
|
|||
|
|||
multiple dates - show the most recent
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. -- Duane Hookom MS Access MVP -- "Carrie" wrote in message ... Hi, We have a database that tracks a number of different actions that each have a due date and a reminder date. The actions a 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! |
#3
|
|||
|
|||
multiple dates - show the most recent
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" wrote: 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. -- Duane Hookom MS Access MVP -- "Carrie" wrote in message ... Hi, We have a database that tracks a number of different actions that each have a due date and a reminder date. The actions a 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! |
#4
|
|||
|
|||
multiple dates - show the most recent
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. -- Duane Hookom MS Access MVP "Carrie" wrote in message ... 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" wrote: 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. -- Duane Hookom MS Access MVP -- "Carrie" wrote in message ... Hi, We have a database that tracks a number of different actions that each have a due date and a reminder date. The actions a 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple dates in Criteria section | tuhoegirl | Running & Setting Up Queries | 2 | June 21st, 2005 05:40 AM |
Multiple dates | RogueIT | Running & Setting Up Queries | 2 | June 17th, 2005 09:12 PM |
show dates by the week... | andrew v via AccessMonster.com | Using Forms | 1 | March 25th, 2005 02:45 AM |
show individual reoccurring dates in table format | cindi | Calendar | 0 | January 13th, 2005 05:38 PM |
How do you show multiple calendars in Outlook Today? | lecarlson | Calendar | 0 | December 2nd, 2004 01:59 AM |