Hi,

I am working on a membership database with a colleague. When someone's
membership expires, we want to archive their membership record into an
archive table and we can do this using an append and then a delete query.

Our problem is that some of the records we have in our membership table have
"child" records in a box office table, where we can record which events the
member has bought tickets for.

We are enforcing referential integrity, which I know must be causing this
problem. We are not cascading delete related fields.

My question is, if we relate the archived table to the box office table, a
relationship would still exist. However, would Access be happy with us
deleting the member having appended the record to the box office table? I
suspect not. We wish to automate this via a drop down box on a form to use
as the criteria for the append and delete queries and create a macro to run
them together (which I am happy doing, but we're having the referential
integrity problem). If we are successful in this route, we will need to be
able to delete the record without having to remove the table relationships
first.

If anyone has any ideas, or if I'm making it very difficult when there's an
easier way, then we'd be really grateful for any help.

Thanks.

Take care,

Karen

KarenF wrote:
> Hi,
>
> I am working on a membership database with a colleague. When someone's
> membership expires, we want to archive their membership record into an
> archive table and we can do this using an append and then a delete query.
>
> Our problem is that some of the records we have in our membership table have
> "child" records in a box office table, where we can record which events the
> member has bought tickets for.
>
> We are enforcing referential integrity, which I know must be causing this
> problem. We are not cascading delete related fields.
>
> My question is, if we relate the archived table to the box office table, a
> relationship would still exist. However, would Access be happy with us
> deleting the member having appended the record to the box office table? I
> suspect not. We wish to automate this via a drop down box on a form to use
> as the criteria for the append and delete queries and create a macro to run
> them together (which I am happy doing, but we're having the referential
> integrity problem). If we are successful in this route, we will need to be
> able to delete the record without having to remove the table relationships
> first.
>
> If anyone has any ideas, or if I'm making it very difficult when there's an
> easier way, then we'd be really grateful for any help.
>
> Thanks.
>
> Take care,
>
> Karen

Your's is a very practical situation. I've even thought about it recently.

Why not send the child records to a temporary table and delete them from
the many-table before doing your append and delete queries on the
one-table? When you "INSERT" the child records back into the
many-table, the AutoNumber key field, if listed, will revert back to its
original value. I've tried it and it appears to work. I'm thinking
about trying this idea as part of a more complicated situation involving
multiple relationships. Despite my own advice that Access is not
optimal for Bill of Material type hierarchies I seem to be getting
caught into using Access for exactly that.

James A. Fortune
(E-Mail Removed)

Karen,

In my humble opinion, there is normally no need to go to these lengths,
unless you are talking very large numbers of records. It is normally
much simpler to add an additional field to your membership table, Yes/No
data type, to indicate Expiry. Then you can use a Criteria on this
field in your queries to excluse or include expired members on your
forms and reports.

--
Steve Schapel, Microsoft Access MVP

KarenF wrote:
> Hi,
>
> I am working on a membership database with a colleague. When someone's
> membership expires, we want to archive their membership record into an
> archive table and we can do this using an append and then a delete query.
>
> Our problem is that some of the records we have in our membership table have
> "child" records in a box office table, where we can record which events the
> member has bought tickets for.
>
> We are enforcing referential integrity, which I know must be causing this
> problem. We are not cascading delete related fields.
>
> My question is, if we relate the archived table to the box office table, a
> relationship would still exist. However, would Access be happy with us
> deleting the member having appended the record to the box office table? I
> suspect not. We wish to automate this via a drop down box on a form to use
> as the criteria for the append and delete queries and create a macro to run
> them together (which I am happy doing, but we're having the referential
> integrity problem). If we are successful in this route, we will need to be
> able to delete the record without having to remove the table relationships
> first.
>
> If anyone has any ideas, or if I'm making it very difficult when there's an
> easier way, then we'd be really grateful for any help.
>
> Thanks.
>
> Take care,
>
> Karen

Steve Schapel wrote:
> Karen,
>
> In my humble opinion, there is normally no need to go to these lengths,
> unless you are talking very large numbers of records. It is normally
> much simpler to add an additional field to your membership table, Yes/No
> data type, to indicate Expiry. Then you can use a Criteria on this
> field in your queries to excluse or include expired members on your
> forms and reports.
>

I agree that when searching on a single table with proper indexing it
will take a huge number of records in that table before a noticeable
slowdown in performance occurs. If you're joining five tables in a
query and three of those tables have 10 years of data instead of one
year of data, the time it takes to retrieve the query results, even with
indexing, can be from an order of magnitude to two orders of magnitude
slower (10 to 100 times slower). So your advice, although correct,
should be taken carefully, keeping an eye on performance.

James A. Fortune
(E-Mail Removed)