Combining Tables from Different Databases

[FIECA]

I created a database for my company, tracking information related to our
projects. However, I just discovered that once upon a time someone else had
created a similar database. Some of the data is the same, but some is
different. In particular the old database has some fields collecting
information that I agree would be useful. If the table in my current
database and the old database share a field in common (i.e. our project
number), could I import the extra fields from the other database and somehow
incorporate them into my existing table? I know these questions are kind of
vague, but I would appreciate any dialog in how I might accomplish the
combination of the information in the two databases.

Thanks,

Tom Pratt

 

[Jason Lepack]

It's called a "link table". It can be found in the new tables menu.

Cheers,
Jason Lepack

 

[Pat Hartman \(MVP\)]

As long as there is a 1-1 relationship between the tables on project number
you will be able to incorporate the old data.
1. Backup the database
2. Open the table in design view and add the new columns to the table you
want to add data to
3. Link to the source table in the old database
4. Create an update query that joins the two tables on project number and
updates the fields in the new table with the values from the fields in the
linked table
5. Verify the results
6. Delete the link

 

[FIECA]

Thanks everyone, I will do as suggested and let you know how it turns
out...?!?

 

[FIECA]

Actually, when I spent more time reading your post, I had one other
question.

The old database from which I will be importing information will not have
all of the projects that I have in the new database.
Additionally, there may be some project numbers which occur in the old
database which are not in the new one.

To illustrate...

Old databse will have projects from year 2000 through year 2004
New database will have projects from year 2002 through present

So there will be some overlap, but extra projects that do not correlate both
on the early side and on the late side (if this makes any sense)...

Will this create a problem??

 

[FIECA]

Actually, when I spent more time reading your answers, I had one other
question.

The old database from which I will be importing information will not have
all of the projects that I have in the new database.
Additionally, there may be some project numbers which occur in the old
database which are not in the new one.

To illustrate...

Old databse will have projects from year 2000 through year 2004
New database will have projects from year 2002 through present

So there will be some overlap, but extra projects that do not correlate both
on the early side and on the late side (if this makes any sense)...

Will this create a problem??

 

[Pat Hartman \(MVP\)]

When you join the old table to the new table, only records that exist in
both tables will be returned so only those rows in the new table will be
updated.