Moving records to another table

[Guest]

I built two members of our team each a work file. Then I also have a
contracts database table that holds contract informaton. What happens is my
co-workers record ALL their work into their individual tables. Only some of
that information from their work table makes it into the contracts table
(whether or not it's a contract and it's completed). However, now it's a
manual process. They begin entering the info. into their work table while
it's in negotiations. Then once a contract is completed they enter it into
the contracts table. They would actually prefer if that they could "click a
button" and make the record from their work table appear into contracts
table. Please be aware that the tables don't capture all the same fields.
They just want the fields that are similar populated into the contracts table
(from their work table). Is this possible? If so, is there anyone that
could help me out? Thanks in advance.

 

[Guest]

Post the structures for your tables - Contract, individual, etc.
Show the datatype and primary keys.

 

[Guest]

Here's the info for Contracts Table. I hope this is what you are needing to
see. If not, please let me know. Thanks for takin g the time to help out.

Customer - Text
Agreement - Text
Regions - Text
Global - Yes/No
Effective - Date/Time
End - Date/Time
Perpetual - Yes/No
Termination - Memo
Terms - Text
Freight - Text
Returns - Memo
P&A - Text

And here's the info for one of the co-workers working table.
Date Received - Date/Time
Customer - Text
Region - Text
Type - Text
Is this a Contract - Yes/No
Global - Yes/No
Hold - Yes/No
Status - Text
Date Completed - Date/Time
Requestor - Text
Description - Memo

 

[John Vinson]

I built two members of our team each a work file. Then I also have a
contracts database table that holds contract informaton. What happens is my
co-workers record ALL their work into their individual tables.

If you have a separate identical table for each worker... you're not
using Access optimally! Consider instead having ONE work table, with
an ID for the worker to specify whose data each record is.

Only some of
that information from their work table makes it into the contracts table
(whether or not it's a contract and it's completed). However, now it's a
manual process. They begin entering the info. into their work table while
it's in negotiations. Then once a contract is completed they enter it into
the contracts table.

Ideally, you should enter and store data only ONCE, and - rather than
copying it to another table - just have a link to that record from
another table.

They would actually prefer if that they could "click a
button" and make the record from their work table appear into contracts
table. Please be aware that the tables don't capture all the same fields.
They just want the fields that are similar populated into the contracts table
(from their work table). Is this possible? If so, is there anyone that
could help me out? Thanks in advance.

An Append query would do this - you could select what fields you
wanted to copy, and which records (those which resulted in a contract
obviously). But as I say, this is a less than ideal design.

John W. Vinson[MVP]

 

[Guest]

That was a first thought but that idea did not work well. So instead I use a
query to join the two work tables through a query in the end to run reports.
They were finding it confusing using separate tables so I created individual
work tables. Is it feasible to do what I am asking - To have only certain
records move from their work table to the contracts table? FYI - The work
table and contracts table must be separate.

 

[John Vinson]

That was a first thought but that idea did not work well. So instead I use a
query to join the two work tables through a query in the end to run reports.
They were finding it confusing using separate tables so I created individual
work tables.

Well, I find that confusing. They were "finding it confusing using
separate tables" so you created separate (individual) tables?

Note that users should basically never see raw tables. Each user could
see a Form, based on a query selecting only their records from a
(common) table. A Query looks like a table, works like a table,
updates like a table - I still do not see ANY benefit from having one
table per worker.

Is it feasible to do what I am asking - To have only certain
records move from their work table to the contracts table? FYI - The work
table and contracts table must be separate.

Yes. You can move certain records from the work table to the contracts
table.

Create a Query based on the work table selecting those records, and
selecting the fields that you need to move. Change it to an Append
query using the query-type icon, or the Query menu option. Select the
contracts table as the target to which you will append the records.
Run the query and it will add those records to the contracts table.

John W. Vinson[MVP]

 

[Guest]

I obviously made a mistake in my typing. They did not like the idea of one
table. I also had a form at first, but they complained it was slower when
updating and preferred the entry into the table itself. So I am curious.
You suggest entering data into a form based off a query. What are the
benefits? Say I go ahead and make one table and run a query based off their
name as suggested. My question will still remain, can I move a record from
their work form/table to the contracts form/table?

 

[John Vinson]

I obviously made a mistake in my typing. They did not like the idea of one
table. I also had a form at first, but they complained it was slower when
updating and preferred the entry into the table itself. So I am curious.
You suggest entering data into a form based off a query. What are the
benefits? Say I go ahead and make one table and run a query based off their
name as suggested. My question will still remain, can I move a record from
their work form/table to the contracts form/table?

A properly designed Form, based on a properly designed Query, based on
a properly indexed Table should be perfectly reasonable in terms of
speed. There'll be a certain amount of time opening the form, but you
can hide that time in the time spent opening the database by using
Tools... Startup to make it the default form.

Table datasheets have VERY limited flexibility and give you as the
developer very little control. And they're risky - a user can
unintentionally (and irreversibly) alter or delete thousands of rows
of data without warning or protection.

And as for your question - the same answer applies. An Append query
(launched from a button on the form, so the user doesn't need to do
anything but "move this") can indeed move a record from their work
table to the contracts table. The form is (again) just a tool, a
window - it doesn't contain any data, but it is how the user should
interact with the data.

John W. Vinson[MVP]