move records from one table to another automatically

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
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?
Click to expand...

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.
 
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.
 
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
 
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
 
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.
 
Back
Top