A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

using a date as archiving trigger



 
 
Thread Tools Display Modes
  #1  
Old October 16th, 2009, 04:23 PM posted to microsoft.public.access.tablesdbdesign
Grasavong
external usenet poster
 
Posts: 13
Default 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  
Old October 16th, 2009, 05:10 PM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default 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  
Old October 16th, 2009, 05:18 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old October 16th, 2009, 07:15 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.