merging data between two tables

[Ryan]

how would I combine data from two tables into one? I have SSN as primary key
in both and want to take a few columns from one and put into the other but
want the data to match to the correct SSN in the table importing to.





"baseballs"

 

[Jeff Boyce]

Ryan

Are you saying "move data from one table to another", or "copy data from one
table to another"?

First, make a backup copy of the database. Make two. Make three, just to
be safe ... you are talking about changing data.

Create a query joining the tables on their shared field(s) (?SSN). Add the
fields from the table without the data.

Convert the query to an Update query. In the Update To space beneath those
'empty' fields, put in a reference to the fields in the table that does have
values. It might look something like:

[table2]![YourFullField]

where table2 is the name of the table with the data in the field, and
"YourFullField" is the name of the field that has data and corresponds to
[table1]![YourEmptyField].

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ntc]

and be aware that databasing SSNs is now considered a potential liability in
regard to identity theft.....

so take appropriate caution and consider deleting it if you don't really
need it....

Ryan

Are you saying "move data from one table to another", or "copy data from one
table to another"?

First, make a backup copy of the database. Make two. Make three, just to
be safe ... you are talking about changing data.

Create a query joining the tables on their shared field(s) (?SSN). Add the
fields from the table without the data.

Convert the query to an Update query. In the Update To space beneath those
'empty' fields, put in a reference to the fields in the table that does have
values. It might look something like:

[table2]![YourFullField]

where table2 is the name of the table with the data in the field, and
"YourFullField" is the name of the field that has data and corresponds to
[table1]![YourEmptyField].

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

how would I combine data from two tables into one? I have SSN as primary
key
in both and want to take a few columns from one and put into the other but
want the data to match to the correct SSN in the table importing to.





"baseballs"

 

[a a r o n _ k e m p f]

uh, Jet might not support that many records-- so it is best to move to
SQL Server

and what this dude says about liability-- he doesn't know what he's
talking about.
Basically-- Jet is impossible to secure-- so anything that is
questionable like this-- should be moved to a database that supports
security.

like SQL Server, for example

 

[BruceM]

Uh, who said anything about the number of records? And, uh, the limitation
is the size of the database in any case.

And yes, the person understands the liability implications, and tried to
convey that to the OP.

uh, Jet might not support that many records-- so it is best to move to
SQL Server

and what this dude says about liability-- he doesn't know what he's
talking about.
Basically-- Jet is impossible to secure-- so anything that is
questionable like this-- should be moved to a database that supports
security.

like SQL Server, for example

 

[a a r o n _ k e m p f]

no dude-- you don't get it.

there isn't a single drop of data, anywhere in the world-- that has
_ZERO_ security requirements.

Thus-- if you build a database-- use a database with _SOME_ sort of
security.
Jet doesn't have any security.

Use SQL Server for everything, it's easier to support ONE database
than a dozen unmaintainable databases.
Jet doesn't meet _ANY_ requirements

You can't run profiler with Jet
You can't run DTS with Jet
You can't secure Jet.

So it's a little more severe than 'don't use jet in this one location'

 

[a a r o n _ k e m p f]

and one record + one record = might be too many records for jet!
and one record + one record = might be too many records for jet!
and one record + one record = might be too many records for jet!
and one record + one record = might be too many records for jet!
and one record + one record = might be too many records for jet!
and one record + one record = might be too many records for jet!