update query vs make query

[pat67]

Question. I have a table in one Db that I link to another. So from Db
A to Db B. What I need to do is add a field from the linked table to
another table in Db B. Can I use an Update query or do i need to do a
make query and join both tables?

 

[John W. Vinson]

Question. I have a table in one Db that I link to another. So from Db
A to Db B. What I need to do is add a field from the linked table to
another table in Db B. Can I use an Update query or do i need to do a
make query and join both tables?

Please explain. Are you trying to change the structure of the table? If so,
neither solution you suggest will work. An Update query changes the CONTENTS
of existing fields in a table; a MakeTable query creates a brand new table.

What's the context? What are these tables ? What does this new field
represent, and what do you want as the end result?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Please explain. Are you trying to change the structure of the table? If so,
neither solution you suggest will work. An Update query changes the CONTENTS
of existing fields in a table; a MakeTable query creates a brand new table.

What's the context? What are these tables ? What does this new field
represent, and what do you want as the end result?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I know a make table creates a new table and i know i can use it. I
have to add data from one table to another. I wasn't sure if an update
query would work. basically there is a table of parts that i need to
add another field to. so rather than type in 10,000 lines of data, i
need to add a field from table B to table A where the parts are the
same. I know i can do it by joining the parts field and doing a make
table. Can i use an append query? I am not sure i can join table A and
table B and then append table A while table A is open in a query

 

[Bob Barrows]

I know a make table creates a new table and i know i can use it. I
have to add data from one table to another. I wasn't sure if an update
query would work. basically there is a table of parts that i need to
add another field to. so rather than type in 10,000 lines of data, i
need to add a field from table B to table A where the parts are the
same. I know i can do it by joining the parts field and doing a make
table. Can i use an append query? I am not sure i can join table A and
table B and then append table A while table A is open in a query

If I understand you correctly, a query cannot add a field to an existing
table, a DML query that is. Of course a DDL query can do so with an ALTER
TABLE statement.
What you can do is add a new column with the correct datatype to the table
(using Table Design or an ALTER TABLE statement), then, once the field is
added to the table, you can use an update query to populate it.

Personally, I would not go to this bother without a good reason. At this
stage of the game, it sounds as if you can create a view with a simple join
between the linked table and the local table, and display the contents of
that field in that manner. That way I would not have to worry about
resynchronizing the data if it changes in the linked table. It hardly ever
makes sense to store the same data in two places.
If you plan for this database to be used in a place that cannot connect to
Db B, then import the table instead of linking to it. When you reconnect,
you can run a routine to either
- delete the data from the imported table and use an Append query to add the
data to it again,
- or drop it and import it again.

 

[John W. Vinson]

I know a make table creates a new table and i know i can use it. I
have to add data from one table to another. I wasn't sure if an update
query would work. basically there is a table of parts that i need to
add another field to. so rather than type in 10,000 lines of data, i
need to add a field from table B to table A where the parts are the
same. I know i can do it by joining the parts field and doing a make
table. Can i use an append query? I am not sure i can join table A and
table B and then append table A while table A is open in a query

Why do you need a new table AT ALL?

Can you not simply create a Query joining TableA to TableB by the part
identifier, and use THAT QUERY as the source of your form, report, export,
whatever?

Neither an Append query nor an Update Query will "add a field to Table A".
These Queries move data around within the structure of existing tables. They
do NOT change the structure of the tables - but, if I'm understanding you
aright, you don't NEED to change the structure of either table, just combine
them dynamically in a Query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Why do you need a new table AT ALL?

Can you not simply create a Query joining TableA to TableB by the part
identifier, and use THAT QUERY as the source of your form, report, export,
whatever?

Neither an Append query nor an Update Query will "add a field to Table A"..
These Queries move data around within the structure of existing tables. They
do NOT change the structure of the tables - but, if I'm understanding you
aright, you don't NEED to change the structure of either table, just combine
them dynamically in a Query.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

you are correct I can just join the tables in a query. The reason i
wanted to use a table is once the table is created any report form or
other query will run quickly. There is a large amount of data in the
two initial tables so outputting a query will take time and i didn't
want to export a report or something. So that's why i was going to use
a make query and just create a new table once and be done with it.