Reconciling Two Tables With Similar Information

[FIECA]

I posted this question originally on the tables board, but probably could
use some help from the queries gurus as well. I created a database to track
information on the projects my company gets. However, after building my
entire database, I discovered an old one that someone had created which I
found tracked some additional information which would be good for my
historical records. The problem is that one table has some jobs in it that
the other does not. Additionally, both tables share some information
(fields) but not others. The tables gurus advised me to create a join query
to combine the tables, since there is a one to one correlation between
tables, vis a vis the project number. However, after further consideration,
some of the projects are in the old table that are not in the new one, and
some are in the new one that are not in the old one. i.e. new table =
projects from 2002 through the present, old table = projects from 200
through 2004. Any advice on how to get all of the information from both
tables into one would be greatly appreciated. Also, if it is not possible
to thoroughly combine the two due to no correlation between some project
numbers, how do I create a query to let me know which project numbers don't
correlate between the two tables so I can do some manual transferrence.

Thanks in advance,

Tom Pratt

 

[Gary Walter]

I posted this question originally on the tables board, but probably could
use some help from the queries gurus as well. I created a database to
track information on the projects my company gets. However, after building
my entire database, I discovered an old one that someone had created which
I found tracked some additional information which would be good for my
historical records. The problem is that one table has some jobs in it that
the other does not. Additionally, both tables share some information
(fields) but not others. The tables gurus advised me to create a join
query to combine the tables, since there is a one to one correlation
between tables, vis a vis the project number. However, after further
consideration, some of the projects are in the old table that are not in
the new one, and some are in the new one that are not in the old one. i.e.
new table = projects from 2002 through the present, old table = projects
from 200 through 2004. Any advice on how to get all of the information
from both tables into one would be greatly appreciated. Also, if it is not
possible to thoroughly combine the two due to no correlation between some
project numbers, how do I create a query to let me know which project
numbers don't correlate between the two tables so I can do some manual
transferrence.

If you have a query that "works" for when the project numbers match,
then one *possible* simple solution....

make copy of query and name it "qryLeft"

make another copy of query and name it "qryRight"

Change the "INNER JOIN" in first to "LEFT JOIN"

Change the "INNER JOIN" in second to "RIGHT JOIN"

Start a new query, go to SQL View" and create UNION query

SELECT * FROM qryLeft
UNION
SELECT * FROM qryRight;

save as "qryuniLeftRight"

All that's left is create one last query to pump results of union query
into a new table.

SELECT * INTO newtable FROM qryuniLeftRight;

Above for simplification, but I typically would replace "*" with all field
names.