move records from one table to another automatically

G

Guest

I run a database to track personell. I need to configure it to move
(cut-and-paste)records from my "Contacts" table to a "Transferred" table when
I type the date into the "Date Left" Field of my "Loss" table (which is
linked to the "Contacts" table). I need it to move records from the
"Contacts" table to the "Separated" table when I type a date into the "Date
Left" field and check the "Sep" checkbox on my "Loss" table. How do I do it?
 
J

Joseph Meehan

TempestChante said:
I run a database to track personell. I need to configure it to move
(cut-and-paste)records from my "Contacts" table to a "Transferred"
table when I type the date into the "Date Left" Field of my "Loss"
table (which is linked to the "Contacts" table). I need it to move
records from the "Contacts" table to the "Separated" table when I
type a date into the "Date Left" field and check the "Sep" checkbox
on my "Loss" table. How do I do it?

As other have sort of suggested, maybe you don' t really want to move
them to a different table, maybe just marking their status might be a better
move.
 
A

Allen Browne

Here's the easy way (and what I actually do about 90% of the time).

1. Open your table in design view.
Add a yes/no field to your table, named "Inactive".
In the lower pane, set its Indexed property to "Yes: Duplicates ok".
Save. Close the table.

2. Create a query into this table.
Drag the * into the query grid (so it shows all columns).
Drag the Inactive field into the grid also.
Uncheck the Show box under this field.
In the Criteria, enter:
False
Specify any sorting you want.
Save the query.

3. Open (or create) the form where you enter data into this table.
In form design view, change the RecordSource property to the Query.
Provide an interface (check box or command button or something) so the user
can mark the record inactive.
Save the form.

This form now shows only the records that are not inactive. You can use a
similar form to show the inactive records. To the user, it looks like they
are coming from different sources, but because they are actually in the same
table, there is no need to move them from one table to another. This means
you avoid all the issues about setting up another set of relationships, and
then trying to create UNION queries if you need to compare current data and
older ones.
 
G

Guest

Joseph (and anyone else who can help),

I've read your responses to TempestChante and understand why you might
encourage her not to move records. However, in my situation, I must move
them. It's similar to hers....I have a table for current students and one
for graduates. I must move graduating students out of the current students
table and into the graduates table. Otherwise, my data will be inaccurate
when I run certain reports.

So....how can I move those records?

Thank you,
Arlene
 
V

Van T. Dinh

Moving Records is possible but certainly inefficient and likely to involve
in incorrect Table Structure. Hence, this should be avoided.

There is only *one* entity: Student so you should only have 1 Table.

Whether current or previously graduated are different classes / types of
Students. This should generally be indicated by a Field in the Table for
the entity.

All you have to do is to have a flag (Boolean?) Field in your tblStudent
indicating whether the Student is current or previously graduated (leaving
aside the possibility that this may be a "calculated" value).

Base your Reports on Queries with suitable criteria, e.g. Flag = current,
and your Reports will only get the data related to current Students and not
previoously graduated Students
 
E

Ellie

Hi Allen,

I am tyring to accomplish a similar task. My criteria is to just
move/archive the current record. The problem however, is that my Form isn't
based from a Query; it's based directly from a Table. Can I still accomplish
this task? Thanks.

Happy Hollidays!
Ruth,

P.S. In addition, I would like to create reports for active and inactive
records. As of now, everything shows up on one report, which I don't mind,
but I would also like to have then separated. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top