archiving

[WindAndWaves]

Hi Everyone

I was wondering if anyone has any great ideas on archiving.

I have a database, lets say a sales database. Future transactions are
really important, but past transactions are only important from time to
time.

Therefore, I would like to archive them so that they are not visible during
normal processing, just as and when required (e.g. statistics, the
occasional inquiry).

This I how I thought it would work:

1. I duplicate the table of sales.
2. I identify old sales
3. I copy them into the duplicated table
4. I delete them from the table that was duplicated.

Sound simple, but I already foresee a host of problems:
1. I delete the contact who I sold the goods to, now it does not link any
longer to the archived sales record
2. I always need just that one record that was archived
3. etc....

Ideally, what I would like to do is to keep the records in the same table,
but somehow hide them.

Now I know that you might say that I should use a query. But that would get
very confusing very quickly.

I can also tell you that the sales table is a linked table, could I perhaps
put some sort of selection statement in the link syntax????

As you can see, I am unsure on how to proceed. Is there anyone out there
with any good ideas????

[Allen Browne]

Re: archiving

Leave them in the same tables.
Add a field such as this:
Name: IsDeleted
Type Yes/No
Indexes: Yes (Dupes ok)

Create queries to select the records where IsDeleted is False.
With the indexed field, there is no significant speed penalty, unless you
foresee millions of records in your table.

If you want to move the records anyway, this link shows how to do it safely:
Archive: Move records to another table
at:

http://allenbrowne.com/ser-37.html

As you mentioned, you must move the related records before you can move the
primary records. However, I suggest you DON'T move them at all. Flagging the
records is generally much more efficient and flexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"WindAndWav es" <access@ngaru.c om> wrote in message
news:yx7Jc.205$ N77.120244@news .xtra.co.nz...[color=blue]
> Hi Everyone
>
> I was wondering if anyone has any great ideas on archiving.
>
> I have a database, lets say a sales database. Future transactions are
> really important, but past transactions are only important from time to
> time.
>
> Therefore, I would like to archive them so that they are not visible[/color]
during[color=blue]
> normal processing, just as and when required (e.g. statistics, the
> occasional inquiry).
>
> This I how I thought it would work:
>
> 1. I duplicate the table of sales.
> 2. I identify old sales
> 3. I copy them into the duplicated table
> 4. I delete them from the table that was duplicated.
>
> Sound simple, but I already foresee a host of problems:
> 1. I delete the contact who I sold the goods to, now it does not link any
> longer to the archived sales record
> 2. I always need just that one record that was archived
> 3. etc....
>
> Ideally, what I would like to do is to keep the records in the same table,
> but somehow hide them.
>
> Now I know that you might say that I should use a query. But that would[/color]
get[color=blue]
> very confusing very quickly.
>
> I can also tell you that the sales table is a linked table, could I[/color]
perhaps[color=blue]
> put some sort of selection statement in the link syntax????
>
> As you can see, I am unsure on how to proceed. Is there anyone out there
> with any good ideas????[/color]

[Arno R]

Re: archiving

> Therefore, I would like to archive them so that they are not visible during[color=blue]
> normal processing, just as and when required (e.g. statistics, the
> occasional inquiry).[/color]
You need to elaborate on 'normal processing' here.

IMO the best approach is to leave all sales-records in the same table.
Access can handle lots of records if your tables are properly indexed.
[color=blue]
> Sound simple, but I already foresee a host of problems:
> 1. I delete the contact who I sold the goods to, now it does not link any
> longer to the archived sales record[/color]
If you really have to transfer records to a table let's say 'TabSalesArchiv e' then you must enforce
RI here also!
In that case you just can't delete the contact so your problem nr 1 is solved.
[color=blue]
> Ideally, what I would like to do is to keep the records in the same table,
> but somehow hide them.[/color]
Why would you want to 'hide' records ?
When searching only for records of let's say the last two years, this can be easily done with a
query on a field SalesDate I guess?
[color=blue]
> Now I know that you might say that I should use a query. But that would get
> very confusing very quickly.[/color]
??
[color=blue]
> I can also tell you that the sales table is a linked table, could I perhaps
> put some sort of selection statement in the link syntax????[/color]
No

--
Hope this helps
Arno R

[WindAndWaves]

Re: archiving

thank you both arno and allen, this reinforces what i already thought may be
the best solution: keep them where they are and tick them with a "boxed"