Transferring information from one table to another

[jwr]

My database is the "Orders" database provided with Microsoft Access. The
Orders, Orders Details, Orders by Customer and Invoice all require that I
input an employee name.

Now that I have worked with the forms in more depth, I find that a table
named "Dealer" and "employee" are actually the same info.

How do I go about deleting the Dealer table and copying all of the
information and transf to the Employee table; thus allowing all forms and
subforms, and invoices to function properly???

I have to have the employee table remain. Any time I have attempted to
remove the employee table, my entire database has problems.

Hindsight IS better than foresight!

Thank you in advance,
JR

 

[Allen Browne]

JR, what you are proposing to do makes very good sense from a design
point-of-view, but--as you found--it is not a simple thing once the database
has already been in use.

Presumably your Dealer table has a primary key--perhaps an AutoNumber named
DealerID. Your Employee table will also have an EmployeeID primary key. And
there may be cases where the DealerID is using the same numbers as the
EmployeeID. It is therefore not just a matter of copying the records from
one table to another.

Further, there are probably other related tables that are using the DealerID
and EmployeeID. So any changes have to be made to those tables also.

The process would therefore involve something like this:

1. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Otherwise Access will try to remember the wrong tables and fields.

2. Create a new table that provides the fields from both tables. (I never
know what to call this combined one--usually ends up as tblClient: though
that's not a perfect name, it ends up with corporate entities (companies,
schools, ...) and persons (staff, buyers, ...)).

3. Use an Append query (Append in query design) to copy all the records from
the Dealer table into the new one.

4. Use an Append query to copy all records from the Employee table into the
new one. But this time populate ClientID field with:
1000 + [EmployeeID]
where the number is large enough that it exceeds the largest value in the
DealerID.

5. Break all relationships between the Dealer table and related tables, and
between the Employee table and related tables.

6. Use an Update query to add the 1000 to the value of all EmployeeID
foreign keys throughout the database, so they match the value assigned in
step 3.

7. Create the relations between the new Client table and all the other
related tables.

8. Track down all queries that use the Dealer and Employee tables, and
change them to use the new table instead. There are commercial utilities
such as Speed Ferret that can help in this process. You can copy the SQL
statment out to Notepad and use search'n'replace. This query might help you
trace down the dependencies:
SELECT MSysObjects.Name FROM MSysQueries
INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so
that none of the code and queries can accidentally find them. (Or delete
them if you are feeling brave and have backups.)

10. Change the RecordSource of all affected forms and reports, and the
RowSource of all affected combos and list boxes.

11. Search and replace in code as well.

Whether it's worth that effort for this database is up to you, but the
design is certainly worth keeping in mind for your next one.

 

[jwr]

Before I begin this process, several more points --
There are no records in the employee table; however, the Orders database
requires the employee id to be included in the reports, invoices, etc.

Dealer table contains all the information that I would like to see in
place of employee id.

If I change the employee id to dealer id in design view on, for
instance, the invoice, the report does not work.

Is this the process I need to follow -- OR -- is there a way to change
employee id references to be dealer id?

Whenever I remove the employee id fields, forms and reports do not work.

Thanks in advance,
JR

JR, what you are proposing to do makes very good sense from a design
point-of-view, but--as you found--it is not a simple thing once the database
has already been in use.

Presumably your Dealer table has a primary key--perhaps an AutoNumber named
DealerID. Your Employee table will also have an EmployeeID primary key. And
there may be cases where the DealerID is using the same numbers as the
EmployeeID. It is therefore not just a matter of copying the records from
one table to another.

Further, there are probably other related tables that are using the DealerID
and EmployeeID. So any changes have to be made to those tables also.

The process would therefore involve something like this:

1. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Otherwise Access will try to remember the wrong tables and fields.

2. Create a new table that provides the fields from both tables. (I never
know what to call this combined one--usually ends up as tblClient: though
that's not a perfect name, it ends up with corporate entities (companies,
schools, ...) and persons (staff, buyers, ...)).

3. Use an Append query (Append in query design) to copy all the records from
the Dealer table into the new one.

4. Use an Append query to copy all records from the Employee table into the
new one. But this time populate ClientID field with:
1000 + [EmployeeID]
where the number is large enough that it exceeds the largest value in the
DealerID.

5. Break all relationships between the Dealer table and related tables, and
between the Employee table and related tables.

6. Use an Update query to add the 1000 to the value of all EmployeeID
foreign keys throughout the database, so they match the value assigned in
step 3.

7. Create the relations between the new Client table and all the other
related tables.

8. Track down all queries that use the Dealer and Employee tables, and
change them to use the new table instead. There are commercial utilities
such as Speed Ferret that can help in this process. You can copy the SQL
statment out to Notepad and use search'n'replace. This query might help you
trace down the dependencies:
SELECT MSysObjects.Name FROM MSysQueries
INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so
that none of the code and queries can accidentally find them. (Or delete
them if you are feeling brave and have backups.)

10. Change the RecordSource of all affected forms and reports, and the
RowSource of all affected combos and list boxes.

11. Search and replace in code as well.

Whether it's worth that effort for this database is up to you, but the
design is certainly worth keeping in mind for your next one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My database is the "Orders" database provided with Microsoft Access. The
Orders, Orders Details, Orders by Customer and Invoice all require that I
input an employee name.

Now that I have worked with the forms in more depth, I find that a table
named "Dealer" and "employee" are actually the same info.

How do I go about deleting the Dealer table and copying all of the
information and transf to the Employee table; thus allowing all forms and
subforms, and invoices to function properly???

I have to have the employee table remain. Any time I have attempted to
remove the employee table, my entire database has problems.

Hindsight IS better than foresight!

Thank you in advance,
JR

 

[Allen Browne]

If you have no data in the Employee table, and therefore no related values
in any EmployeeID foreign key in any other table, that simplies the process.
You can just break all the relationships, rename the Dealer table, drop the
Employee talbe, and then create all the relationships to the newly named
table.

As explained in the prior reply, you will need to go through your database
and find every affected query, form, report, macro, and code reference, and
change them all. As you say, it just fouls up and doesn't work until you
make these changes. There are probably many hours work--possibly days--to
achieve that change after the database has already been built.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Before I begin this process, several more points --
There are no records in the employee table; however, the Orders
database
requires the employee id to be included in the reports, invoices, etc.

Dealer table contains all the information that I would like to see in
place of employee id.

If I change the employee id to dealer id in design view on, for
instance, the invoice, the report does not work.

Is this the process I need to follow -- OR -- is there a way to change
employee id references to be dealer id?

Whenever I remove the employee id fields, forms and reports do not work.

Thanks in advance,
JR

JR, what you are proposing to do makes very good sense from a design
point-of-view, but--as you found--it is not a simple thing once the database
has already been in use.

Presumably your Dealer table has a primary key--perhaps an AutoNumber named
DealerID. Your Employee table will also have an EmployeeID primary key. And
there may be cases where the DealerID is using the same numbers as the
EmployeeID. It is therefore not just a matter of copying the records from
one table to another.

Further, there are probably other related tables that are using the DealerID
and EmployeeID. So any changes have to be made to those tables also.

The process would therefore involve something like this:

1. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Otherwise Access will try to remember the wrong tables and fields.

2. Create a new table that provides the fields from both tables. (I never
know what to call this combined one--usually ends up as tblClient: though
that's not a perfect name, it ends up with corporate entities (companies,
schools, ...) and persons (staff, buyers, ...)).

3. Use an Append query (Append in query design) to copy all the records from
the Dealer table into the new one.

4. Use an Append query to copy all records from the Employee table into the
new one. But this time populate ClientID field with:
1000 + [EmployeeID]
where the number is large enough that it exceeds the largest value in the
DealerID.

5. Break all relationships between the Dealer table and related tables, and
between the Employee table and related tables.

6. Use an Update query to add the 1000 to the value of all EmployeeID
foreign keys throughout the database, so they match the value assigned in
step 3.

7. Create the relations between the new Client table and all the other
related tables.

8. Track down all queries that use the Dealer and Employee tables, and
change them to use the new table instead. There are commercial utilities
such as Speed Ferret that can help in this process. You can copy the SQL
statment out to Notepad and use search'n'replace. This query might help you
trace down the dependencies:
SELECT MSysObjects.Name FROM MSysQueries
INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so
that none of the code and queries can accidentally find them. (Or delete
them if you are feeling brave and have backups.)

10. Change the RecordSource of all affected forms and reports, and the
RowSource of all affected combos and list boxes.

11. Search and replace in code as well.

Whether it's worth that effort for this database is up to you, but the
design is certainly worth keeping in mind for your next one.

My database is the "Orders" database provided with Microsoft Access. The
Orders, Orders Details, Orders by Customer and Invoice all require that I
input an employee name.

Now that I have worked with the forms in more depth, I find that a
table
named "Dealer" and "employee" are actually the same info.

How do I go about deleting the Dealer table and copying all of the
information and transf to the Employee table; thus allowing all forms and
subforms, and invoices to function properly???

I have to have the employee table remain. Any time I have attempted to
remove the employee table, my entire database has problems.

Hindsight IS better than foresight!

Thank you in advance,
JR

 

[jwr]

What would I and why would I rename the Dealer table? I don't understand.
I was thinking that if I could locate, perhaps by query, every instance
where Employee ID was used, I could substitute Dealer ID.
Again, I do not know how to do what I am suggesting.

If you have no data in the Employee table, and therefore no related values
in any EmployeeID foreign key in any other table, that simplies the process.
You can just break all the relationships, rename the Dealer table, drop the
Employee talbe, and then create all the relationships to the newly named
table.

As explained in the prior reply, you will need to go through your database
and find every affected query, form, report, macro, and code reference, and
change them all. As you say, it just fouls up and doesn't work until you
make these changes. There are probably many hours work--possibly days--to
achieve that change after the database has already been built.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Before I begin this process, several more points --
There are no records in the employee table; however, the Orders
database
requires the employee id to be included in the reports, invoices, etc.

Dealer table contains all the information that I would like to see in
place of employee id.

If I change the employee id to dealer id in design view on, for
instance, the invoice, the report does not work.

Is this the process I need to follow -- OR -- is there a way to change
employee id references to be dealer id?

Whenever I remove the employee id fields, forms and reports do not work.

Thanks in advance,
JR

JR, what you are proposing to do makes very good sense from a design
point-of-view, but--as you found--it is not a simple thing once the database
has already been in use.

Presumably your Dealer table has a primary key--perhaps an AutoNumber named
DealerID. Your Employee table will also have an EmployeeID primary key. And
there may be cases where the DealerID is using the same numbers as the
EmployeeID. It is therefore not just a matter of copying the records from
one table to another.

Further, there are probably other related tables that are using the DealerID
and EmployeeID. So any changes have to be made to those tables also.

The process would therefore involve something like this:

1. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Otherwise Access will try to remember the wrong tables and fields.

2. Create a new table that provides the fields from both tables. (I never
know what to call this combined one--usually ends up as tblClient: though
that's not a perfect name, it ends up with corporate entities (companies,
schools, ...) and persons (staff, buyers, ...)).

3. Use an Append query (Append in query design) to copy all the records from
the Dealer table into the new one.

4. Use an Append query to copy all records from the Employee table into the
new one. But this time populate ClientID field with:
1000 + [EmployeeID]
where the number is large enough that it exceeds the largest value in the
DealerID.

5. Break all relationships between the Dealer table and related tables, and
between the Employee table and related tables.

6. Use an Update query to add the 1000 to the value of all EmployeeID
foreign keys throughout the database, so they match the value assigned in
step 3.

7. Create the relations between the new Client table and all the other
related tables.

8. Track down all queries that use the Dealer and Employee tables, and
change them to use the new table instead. There are commercial utilities
such as Speed Ferret that can help in this process. You can copy the SQL
statment out to Notepad and use search'n'replace. This query might help you
trace down the dependencies:
SELECT MSysObjects.Name FROM MSysQueries
INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

9. Rename the Dealer and Employee tables to (say) DealerX and

EmployeeX,
so

that none of the code and queries can accidentally find them. (Or delete
them if you are feeling brave and have backups.)

10. Change the RecordSource of all affected forms and reports, and the
RowSource of all affected combos and list boxes.

11. Search and replace in code as well.

Whether it's worth that effort for this database is up to you, but the
design is certainly worth keeping in mind for your next one.

My database is the "Orders" database provided with Microsoft Access. The
Orders, Orders Details, Orders by Customer and Invoice all require

that
I

input an employee name.

Now that I have worked with the forms in more depth, I find that a
table
named "Dealer" and "employee" are actually the same info.

How do I go about deleting the Dealer table and copying all of the
information and transf to the Employee table; thus allowing all forms and
subforms, and invoices to function properly???

I have to have the employee table remain. Any time I have attempted to
remove the employee table, my entire database has problems.

Hindsight IS better than foresight!

Thank you in advance,
JR

 

[Allen Browne]

The renaming is not necessary.

The suggestion was merely an addendum to my previous post, suggesting how to
integrate the previous suggestions into where you were.

There is nothing further I can add that would be of use to where you are up
to here.