Auto fill help

[Scoots987]

What is the trick to allow auto filling?

I have two tables that look something like:

table1
SSN Text
Lname Text
Fname Text

table2
SSN Text
Lname Text
Fname Text

I'm trying to simplify this here. table1 is my data entry
table. If my user types SSN of 111-11-1111 I want the
remainder of the fields to fill from table2. Specifically
Lname and Fname. How do I do this? This doesn't have to
be done through code right? If it matters I am using
Access 2000.

I wish look at both on a table and a form. Wouldn't the
form use the table lookups?

Thanks

 

[Van T. Dinh]

Suggestion: *don't store* Lname and Fname in table1!

You are trying to store redundant data which violates the
Relational Database Design Principles.

Assuming that [SSN] is the PrimaryKey (in table2), all you
need to store in table1 is the SSN because if you know the
SSN, you can look up the Lname and Fname from table2.

HTH
Van T. Dinh
MVP (Access)

 

[Roger Carlson]

Frankly, you don't want to do this. You are thinking like a spreadsheet
user. In a database, you want to store information like this only once, in
your lookup table. The only thing you need to enter into table1 is the SS#
(if that is the primary key of Table2). Later when you need to know who a
particular SS# is (say on a form or report) you can join the tables back
together to see that information.

Storing all that redundant data just leads to bloated databases and data
integrity problems.

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

 

[Guest]

ok, but what about verification and more importantly what
if I send this data out to be processed. Eventually the
people I send it too will need the names and addresses of
the data entered SSN.

Rather than tell what I shouldn't do, tell me what I can
do. SSN was just an example I was using. What if it is
phone number and possible to have dups and need to allow
the user to determine the right one. What about data
entry errors? I need to display the names for
verification.

I am not designing an in house application just a way to
send requested data.

Thanks anyways

 

[Rick Brandt]

ok, but what about verification and more importantly what
if I send this data out to be processed. Eventually the
people I send it too will need the names and addresses of
the data entered SSN.

The you would send them a report or query that joins the tables together.
Tables are the groundwork structures that you use to build your interface
on. Don't expect them to provide a "final results" sort of structure or
you will quickly end up with a large mess.