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
|
|||
|
|||
using a date as archiving trigger
Hello,
I have a question that I'm not sure if it's possible for Access 2003. I'm creating a database where we would like to use a project's end-date as the trigger for archiving the record (row) to an 'archived' or 'completed' table. For example: Suppose Project #10 was finished on 10/10/09. Once someone enters that field (which is called 'Completed Date') with a date, the record is automatically removed from the current table and sent to an 'archived' or 'completed' table. I was wondering if this is possible instead of having to manually cutting each row that has a finished project to the completed table. |
#2
|
|||
|
|||
using a date as archiving trigger
On Oct 16, 10:23*am, Grasavong
wrote: Hello, I have a question that I'm not sure if it's possible for Access 2003. I'm creating a database where we would like to use a project's end-date as the trigger for archiving the record (row) to an 'archived' or 'completed' table. For example: Suppose Project #10 was finished on 10/10/09. Once someone enters that field (which is called 'Completed Date') with a date, the record is automatically removed from the current table and sent to an 'archived' or 'completed' table. I was wondering if this is possible instead of having to manually cutting each row that has a finished project to the completed table. Unless you have a LOT of data (in excess of 2 million rows or so), you shouldn't need to do this... If you filter for archived/non-archived and base queries on that, everything will work fine. Just make sure you index the 'Completed Date' column. You could do what you want in the AfterUpdate event of the CompletedDate control on your form. Something like: -- NOTE: both queries will have the same filter (e.g. = Me.RecordID) DBEngine(0)(0).Execute "appendQuery" DBEngine(0)(0).Execute "deleteQuery" I would STRONGLY recommend testing this out on a junk table. So if you make a mistake you don't mess up your real data. |
#3
|
|||
|
|||
using a date as archiving trigger
There is a better and simpler way to do what you desire.
Add criteria to your queries of [Completed Date] Is Null for all using currrent data. If you need an annual report all projects, open and completed are in the same table to provide yearly totals. If you insist on doing it your way then in a macro named Autoexec have 2 actions. The first to append from current records to archive table all that have a date in the [Completed Date] field. The second action to delete all current records that have a date in the [Completed Date] field. -- Build a little, test a little. "Grasavong" wrote: Hello, I have a question that I'm not sure if it's possible for Access 2003. I'm creating a database where we would like to use a project's end-date as the trigger for archiving the record (row) to an 'archived' or 'completed' table. For example: Suppose Project #10 was finished on 10/10/09. Once someone enters that field (which is called 'Completed Date') with a date, the record is automatically removed from the current table and sent to an 'archived' or 'completed' table. I was wondering if this is possible instead of having to manually cutting each row that has a finished project to the completed table. |
#4
|
|||
|
|||
using a date as archiving trigger
As others have already pointed out, "moving" records is not necessary or
desireable in a relational database. For example, should you wish to take a look at all the projects managed by PersonA, over the last 10 years, do you really want to have to find the old archive tables and add all those records to the ones that are current (or more recent) to find all of PersonA's projects?! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Grasavong" wrote in message ... Hello, I have a question that I'm not sure if it's possible for Access 2003. I'm creating a database where we would like to use a project's end-date as the trigger for archiving the record (row) to an 'archived' or 'completed' table. For example: Suppose Project #10 was finished on 10/10/09. Once someone enters that field (which is called 'Completed Date') with a date, the record is automatically removed from the current table and sent to an 'archived' or 'completed' table. I was wondering if this is possible instead of having to manually cutting each row that has a finished project to the completed table. |
Thread Tools | |
Display Modes | |
|
|