Importing from one table to another within the same database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Does anyone have an idea of how to automatically import data from one table
to another within the same database, based on just one of the fields filled
out in one table. Both the tables have same fields names and one of the
tables has all the data, but one of the tables has only one field filled out
with exact same data as the other. I am looking for a way to automatically
fill out data for the rest of the fields for the corresponding data already
present in the incomplete table,
Thank You
 
RB,

I will assume that the table with all the data is a linked table, and you
are trying to update a local table with data you are basically importing from
somewhere else.

Assuming that both tables contain the same primary key values (how did the
table you are updating get these values and not the other values), and the PK
field name is [ID], the SQL would look something like:

UPDATE tblDestination AS D
INNER JOIN tblSource AS S
ON D.ID = S.ID
SET D.Field2 = S.Field2,
D.Field3 = S.Field3,
D.Field4 = S.Field4

To build this in the query grid, add your destination table to the grid,
then add the source table. Join them Destination to Source on the ID field.

If you want to assign aliases to the tables to make this process, now would
be a good time. Right click on the table name, select properties and then in
the Alias box, give the table a short name (alias) that you will refer to it
as in this query. I us D and S to define the destination and Source tables.

Add all of the destination fields except the ID field to the grid, and
change the query to an update query. In the update row of the query grid
type .[FieldName] where you replace the "FieldName" with each of the field
names.

Save the query and backup the database. run the query.

HTH
Dale
 
Could you explain a bit more about why you want two copies of the same data
in your database?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
One of the tables has the entire data, the other table is linked to a form
where users simply add the seriel numbers. This seriel numbers are
automatically entered in the other table, I need the all the rest of the
fields in the other table to automatically fill out from the complete table
based on the present seriel numbers in the other table.
Thanks
 
Yeah i'm using an update query, linked the 2 identical fields in the tables,
not quite sure what should go in the "update to" row of the design view of
the query. I know it's the [tablename].[fieldname] but for some reason i'm
not getting the results
Please help .
 
I'm sorry. I wasn't completely clear. If you have two tables, each of
which has data you want to see, you can use a select query to join them (no
updating required).

Again, why does data in table1 ALSO have to be in table2.

Regards

Jeff Boyce
Microsoft Office/Access MVP

RB86 said:
Yeah i'm using an update query, linked the 2 identical fields in the
tables,
not quite sure what should go in the "update to" row of the design view of
the query. I know it's the [tablename].[fieldname] but for some reason i'm
not getting the results
Please help .

Jeff Boyce said:
Why not use a query to join the two?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
hey, sorry for the confusion,
i actually got the update query to work for me pretty much the way i wanted
it to work. but now there's another limitation.
actually i got one table that holds the entire product data. i created a
user-friendly order form that inputs data to table 2 for my supervisor to see
the orders placed by students. the way it works is , the students search for
the products from the main table and feed in only the seriel numbers in the
order form. my supervisor wanted table 2 to automatically fill out the rest
of the fields for the records based on seriel numbers alone, from the main
table. i got that working . but there's another form(form B) that the
students use to place orders for products that are not present in the main
database, for this form they have to manually fill out all the fields for the
order they're placing.Both the forms r linked to table 2 . now when i run the
update query, it erases all the records entered from form B since those
records don't have a seriel number, my supervisor wants to assign the seriel
numbers to these new products later. is there a way that the query only
updates the records with seriel numbers only and doesn't affect the records
without the seriel number, in table 2.
Thanks for your assistance so far.


Jeff Boyce said:
I'm sorry. I wasn't completely clear. If you have two tables, each of
which has data you want to see, you can use a select query to join them (no
updating required).

Again, why does data in table1 ALSO have to be in table2.

Regards

Jeff Boyce
Microsoft Office/Access MVP

RB86 said:
Yeah i'm using an update query, linked the 2 identical fields in the
tables,
not quite sure what should go in the "update to" row of the design view of
the query. I know it's the [tablename].[fieldname] but for some reason i'm
not getting the results
Please help .

Jeff Boyce said:
Why not use a query to join the two?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the tables has the entire data, the other table is linked to a
form
where users simply add the seriel numbers. This seriel numbers are
automatically entered in the other table, I need the all the rest of
the
fields in the other table to automatically fill out from the complete
table
based on the present seriel numbers in the other table.
Thanks

:

Could you explain a bit more about why you want two copies of the same
data
in your database?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
Does anyone have an idea of how to automatically import data from
one
table
to another within the same database, based on just one of the fields
filled
out in one table. Both the tables have same fields names and one of
the
tables has all the data, but one of the tables has only one field
filled
out
with exact same data as the other. I am looking for a way to
automatically
fill out data for the rest of the fields for the corresponding data
already
present in the incomplete table,
Thank You
Click to expand...
Click to expand...
Click to expand...
 
I'll suggest that you post a "new" question to the 'group. That way, you'll
get more eyes and brains working on it.

Regards

Jeff Boyce
Microsoft Office/Access MVP


RB86 said:
hey, sorry for the confusion,
i actually got the update query to work for me pretty much the way i
wanted
it to work. but now there's another limitation.
actually i got one table that holds the entire product data. i created a
user-friendly order form that inputs data to table 2 for my supervisor to
see
the orders placed by students. the way it works is , the students search
for
the products from the main table and feed in only the seriel numbers in
the
order form. my supervisor wanted table 2 to automatically fill out the
rest
of the fields for the records based on seriel numbers alone, from the main
table. i got that working . but there's another form(form B) that the
students use to place orders for products that are not present in the main
database, for this form they have to manually fill out all the fields for
the
order they're placing.Both the forms r linked to table 2 . now when i run
the
update query, it erases all the records entered from form B since those
records don't have a seriel number, my supervisor wants to assign the
seriel
numbers to these new products later. is there a way that the query only
updates the records with seriel numbers only and doesn't affect the
records
without the seriel number, in table 2.
Thanks for your assistance so far.


Jeff Boyce said:
I'm sorry. I wasn't completely clear. If you have two tables, each of
which has data you want to see, you can use a select query to join them
(no
updating required).

Again, why does data in table1 ALSO have to be in table2.

Regards

Jeff Boyce
Microsoft Office/Access MVP

RB86 said:
Yeah i'm using an update query, linked the 2 identical fields in the
tables,
not quite sure what should go in the "update to" row of the design view
of
the query. I know it's the [tablename].[fieldname] but for some reason
i'm
not getting the results
Please help .

:

Why not use a query to join the two?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the tables has the entire data, the other table is linked to
a
form
where users simply add the seriel numbers. This seriel numbers are
automatically entered in the other table, I need the all the rest of
the
fields in the other table to automatically fill out from the
complete
table
based on the present seriel numbers in the other table.
Thanks

:

Could you explain a bit more about why you want two copies of the
same
data
in your database?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
Does anyone have an idea of how to automatically import data from
one
table
to another within the same database, based on just one of the
fields
filled
out in one table. Both the tables have same fields names and one
of
the
tables has all the data, but one of the tables has only one field
filled
out
with exact same data as the other. I am looking for a way to
automatically
fill out data for the rest of the fields for the corresponding
data
already
present in the incomplete table,
Thank You
Click to expand...
Click to expand...
Click to expand...
 
thanks for your suggestion and thanks all along . i got it figured out.
u guys r great.

Jeff Boyce said:
I'll suggest that you post a "new" question to the 'group. That way, you'll
get more eyes and brains working on it.

Regards

Jeff Boyce
Microsoft Office/Access MVP


RB86 said:
hey, sorry for the confusion,
i actually got the update query to work for me pretty much the way i
wanted
it to work. but now there's another limitation.
actually i got one table that holds the entire product data. i created a
user-friendly order form that inputs data to table 2 for my supervisor to
see
the orders placed by students. the way it works is , the students search
for
the products from the main table and feed in only the seriel numbers in
the
order form. my supervisor wanted table 2 to automatically fill out the
rest
of the fields for the records based on seriel numbers alone, from the main
table. i got that working . but there's another form(form B) that the
students use to place orders for products that are not present in the main
database, for this form they have to manually fill out all the fields for
the
order they're placing.Both the forms r linked to table 2 . now when i run
the
update query, it erases all the records entered from form B since those
records don't have a seriel number, my supervisor wants to assign the
seriel
numbers to these new products later. is there a way that the query only
updates the records with seriel numbers only and doesn't affect the
records
without the seriel number, in table 2.
Thanks for your assistance so far.


Jeff Boyce said:
I'm sorry. I wasn't completely clear. If you have two tables, each of
which has data you want to see, you can use a select query to join them
(no
updating required).

Again, why does data in table1 ALSO have to be in table2.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yeah i'm using an update query, linked the 2 identical fields in the
tables,
not quite sure what should go in the "update to" row of the design view
of
the query. I know it's the [tablename].[fieldname] but for some reason
i'm
not getting the results
Please help .

:

Why not use a query to join the two?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One of the tables has the entire data, the other table is linked to
a
form
where users simply add the seriel numbers. This seriel numbers are
automatically entered in the other table, I need the all the rest of
the
fields in the other table to automatically fill out from the
complete
table
based on the present seriel numbers in the other table.
Thanks

:

Could you explain a bit more about why you want two copies of the
same
data
in your database?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
Does anyone have an idea of how to automatically import data from
one
table
to another within the same database, based on just one of the
fields
filled
out in one table. Both the tables have same fields names and one
of
the
tables has all the data, but one of the tables has only one field
filled
out
with exact same data as the other. I am looking for a way to
automatically
fill out data for the rest of the fields for the corresponding
data
already
present in the incomplete table,
Thank You
Click to expand...
Click to expand...
Click to expand...
 
Back
Top