Cutting a record from one table and moving it to another

[Claire]

Hello all,
I have two tables I need to manipulate. The first (ClosedJobs) contains
information about a job when it closes. The second (ReopenedJobs)should have
the information about a job if it needs to be opened again. I would like to
use a form to enter the job # that is reopening, the date it is reopening,
and the reason for the reopening. This information should go into the
ReopenedJobs table (that part I can do no problem) along with the data in the
ClosedJobs table (this is where I'm getting a bit stuck). Then, the record
for that job should be removed from the ClosedJobs table, seeing that it is
no longer closed (I have ideas here, but have never programmed a record
deletion before). I can think of a way to update the ReopenedJobs table
where I put in a text box for each field that will be added to the
ReopenedJobs table, but I'm hoping that there's another way to do it, perhaps
with an append function somewhere?

Note: The ClosedJobs table is a dimensional table. Once a job is closed it
can not be changed, and so information is static. Except of course if we
need to change the job, in which case it needs to be removed from the
ClosedJobs table to be looked at along with the normalized updated tables.

Thanks for any suggestions you may have, and please post if any
clarifications would be helpful.

Thanks,
Claire

 

[fredg]

Hello all,
I have two tables I need to manipulate. The first (ClosedJobs) contains
information about a job when it closes. The second (ReopenedJobs)should have
the information about a job if it needs to be opened again. I would like to
use a form to enter the job # that is reopening, the date it is reopening,
and the reason for the reopening. This information should go into the
ReopenedJobs table (that part I can do no problem) along with the data in the
ClosedJobs table (this is where I'm getting a bit stuck). Then, the record
for that job should be removed from the ClosedJobs table, seeing that it is
no longer closed (I have ideas here, but have never programmed a record
deletion before). I can think of a way to update the ReopenedJobs table
where I put in a text box for each field that will be added to the
ReopenedJobs table, but I'm hoping that there's another way to do it, perhaps
with an append function somewhere?

Note: The ClosedJobs table is a dimensional table. Once a job is closed it
can not be changed, and so information is static. Except of course if we
need to change the job, in which case it needs to be removed from the
ClosedJobs table to be looked at along with the normalized updated tables.

Thanks for any suggestions you may have, and please post if any
clarifications would be helpful.

Thanks,
Claire

You're making much too much out of this.
Add a new check box field to the table.
Name it "chkClosed"
Yes/No datatype
Include this field in your data form.

When a job is closed, simply place a check mark in this field.
If the job is later re-opened simply uncheck the field.

You can then simply filter the records in the form to show either only
Open jobs or only Closed jobs ....
Or base the form on a query that returns only the open or the closed
records, whichever ones you wish.

 

[Jack Leach]

Just in case you're not aware, Allen Browne has an extensive collection of
examples and procedures for doing much of this type of work.
http://www.allenbrowne.com/tips.html

However, for your particular scenario, I would actually suggest keeping
these files in the same table rather than moving to a 'closed' table.
Essentially its the same information, whether the job is opened or close, you
just want to filter for open ones when using your standard data entry.
Include a field towards the front of your table (index it - duplicates OK),
and use it as a flag for job status.

So you do that, create a query (SELECT * FROM tblJobs WHERE fldClosed =
False) to base all of your standard job operations from. Set up the rework
as the many side of a one to many between jobs and reworks (one job can
technically be opened for rework many times, most organizations won't have
any need to opened seperate jobs against a Rework ID), and store any rework
related information here.

At some point in time, you probably do want to get rid of closed records, to
keep the tables in a managable size. Incidentally, Allen provides a great
archive resource on his site (http://www.allenbrowne.com/ser-37.html) which
can be used to purge old records.

If you want to always retain the information from the first time the job was
closed (e.g. save the original data as well as the updated 'closed' job), see
Allen's Audit Trail example. It's a little more advanced, requiring a basic
understanding of the Delete events on a form, but will track and store
changes made to any data for later recall.

2pennies
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)