Lookup & Copy Record Fields from Table to Table

[oakleaf8]

I have two tables in my Access database. The first is a table (tbl1) imported daily from a large database. I want the ability to lookup records from it, choose the applicable record and populate fields into a new record in the second table (tbl2).

Specifically, let's say I need to manage a "Special Request" process that has various data entry points per record and several reports to print. This process is initiated when a person calls in with a special request. I need to create a new "special request" record in tbl2 which leads to my question:

Given the person's last name and last four of SSN what is the best way to filter records from tbl1 (from fields tbl1.LName & tbl1.SSN), select the correct record from the list and copy several fields from the record in tbl1 to a new record in tbl2 to ensure data integrity.

Thanks!

 

[dhookom]

Please share the significant field names and identify primary and foreign keys. The ability to run update queries involving two or more fields typically relies on the primary key fields.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Use an append query, something like..

SELECT INTO tbl2(fieldnames)
(SELECT fieldnames FROM tbl1
WHERE LName = "Jones" and right(SSN,4) = "1234")

Randy

 

[oakleaf8]

Tbl1 is one of several imported tables from various existing databases that are converted and imported daily into an Access database on a large network server. I link to it. Tbl1 has no primary keys nor indexed fields. So when I do search on it I can get duplicate records.

What I'll probably do is from the data entry form (Frm2) for tbl2 add a lookup button that when pressed will allow me to enter "Last Name" and "Last 4 SSN" and show appropriate record(s) from Tbl1. I want to select the correct record (if it exists) and select "Populate Frm2 fields from tbl1". Can it put my lookup results in a tab on Frm2?