How to move a record within an Access 97 database?

[Jan and Hugh Tonkin]

I'm working on a membership database in Access 97 which has 4 datasets, the
main one listing current members.

When somebody ceases membership, I need to move that member's details from
the current members' dataset to one of the other lists within the
membership database.

At the present time we're having to laboriously enter the ex-member's
details in the new dataset and then delete his/her details from the current
member's set.

Is it possible to do this? I've worked on this for about 12 months without
success!

HughTonkin

[Keith Wilby]

"Jan and Hugh Tonkin" <jh******@esc.net.au> wrote:

When somebody ceases membership, I need to move that member's details
from the current members' dataset to one of the other lists within the
membership database.

At the present time we're having to laboriously enter the ex-member's
details in the new dataset and then delete his/her details from the
current member's set.

Yes you could do this, but why bother? Have you considered having all
member details in the same table with a "CurrentMember" (or some-such)
boolean field then querying it accordingly to get your two recordsets?

Just a thought :o)

Regards,
Keith.

[Tim Marshall]

Jan and Hugh Tonkin wrote:

I'm working on a membership database in Access 97 which has 4 datasets, the
main one listing current members.
Presumeably you mean tables.
When somebody ceases membership, I need to move that member's details from
the current members' dataset to one of the other lists within the
membership database.

This is not an adviseable design. A good rule of thumb is never use
duplicate tables with the same data structure.

Keep all members in the same table and add a field called MBR_ACTIVE or
something. Make it a boolean data type and true is the member is
active, false means the member is no longer a member. You can even
include a MBR_ACTIVE_COMMENTS text (or memo) field to write information
as to why the member is no longer active.

You should also be using forms to enter data if you are not already.
never have a user enter data directly into a table.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto