Merging records in Access

S

SD

I have two tables in Access...one is a master file that
lists ID numbers and names of an entire group, while the
other only lists the IDS and names of a select group.
What I want to do is somehow flag the IDs in the master
file that match the IDs in the select file, but I haven't
been able to find a way to do this. Any suggestions?
 
R

Richard Swen

SD said:
I have two tables in Access...one is a master file that
lists ID numbers and names of an entire group, while the
other only lists the IDS and names of a select group.
What I want to do is somehow flag the IDs in the master
file that match the IDs in the select file, but I haven't
been able to find a way to do this. Any suggestions?
Click to expand...

Have you tried to do a union on the two tables. This will
list the records from both of the tables. Once you have
created the union query, you can create a new table from
the query, and either use it or replace the old table.
Below is information on using a vanilla UNION statement.

UNION
The UNION command is used to select related information from
two tables, much like the JOIN command. However, when using
the UNION command all selected columns need to be of the same
data type.

Note: With UNION, only distinct values are selected.

SQL Statement 1

UNION

SQL Statement 2;

Employees_Norway:

Employee_ID E_Name

01 Hansen, Ola

02 Svendson, Tove

03 Svendson, Stephen

04 Pettersen, Kari

Employees_USA:

Employee_ID E_Name

01 Turner, Sally

02 Kent, Clark

03 Svendson, Stephen

04 Scott, Stephen



Using the UNION Command
Example
List all different employee names in Norway and USA:

SELECT E_Name FROM Employees_Norway

UNION

SELECT E_Name FROM
Employees_USA;Result

Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Scott, Stephen



HTH

Thank you,
Richard Swen
 
D

Doug Munich

You can make a query to return the records in the main table that match any
record in the second table like:

select * from tblMainNames where ID in (select ID from tblSubNames);

Then if you needed to flag these you could change this to an update query
where you update a flag field.

Doug
 
Top