Make Table Query Assistance

[jkostenbader]

Need some assistance with a Make Table Query.

I have two tables that I want to combine data in and create another
table. Here is the simplified structure:

Table 1
Old MRN
New MRN

Table 2
Old MRN


I want to create a query that takes the first record in table 2, loops
through table 1 finding a matching "Old MRN" and write both Old MRN
and New MRN to a new table.

"Old MRN" may repeat itself many times in both tables. New MRN is
unique to each record. In the end the record count in the newly
created table should equal the record count in Table 2. If I've left
off critical data please let me know.

I'd appreciate a head start in the right direction

Respectfully

John

 

[Guest]

Try this --
SELECT [Table 2].[Old MRN], [Table 1].[New MRN] INTO [Table 3]
FROM [Table 2] INNER JOIN [Table 1] ON [Table 2].[Old MRN] = [Table 1].[Old
MRN]
GROUP BY [Table 2].[Old MRN], [Table 1].[New MRN];