Table/Forms Field Population

[Scott Nash]

Hello,

I have a table (table1) with 4 fields. ID, FirstName, LastName, Status. I
am in the process of creating a second table (table2). Table2 needs to
include the values from table1.

What I'd like to be able to accomplish is, in table2, a field would contain
a drop down list of ID's from table1. I would select the ID and the First
Name, LastName and Status associated with that ID would be automatically
populated into table2. Each ID is unique, so there are no duplicates.

Any ideas or suggestions would be appreciated.

Thanks.

 

[Carl Rapson]

Hello,

I have a table (table1) with 4 fields. ID, FirstName, LastName, Status.
I
am in the process of creating a second table (table2). Table2 needs to
include the values from table1.

What I'd like to be able to accomplish is, in table2, a field would
contain
a drop down list of ID's from table1. I would select the ID and the First
Name, LastName and Status associated with that ID would be automatically
populated into table2. Each ID is unique, so there are no duplicates.

Any ideas or suggestions would be appreciated.

Thanks.

Suggestion: don't do it. As long as you store the ID in table2, there is no
reason to copy the remaining information from table1 into table2. You can
always fetch the rest of the information whenever needed (in a query, for
example, by joining the two tables on the ID field).

If you positively, absolutely HAVE to copy the information, put code in the
AfterUpdate event of the combobox control containing the IDs to copy the
remaining fields.

Carl Rapson

 

[John W. Vinson]

Hello,

I have a table (table1) with 4 fields. ID, FirstName, LastName, Status. I
am in the process of creating a second table (table2). Table2 needs to
include the values from table1.

No. It certainly should NOT store the data redundantly in a second table.

Relational databases use the "Grandmother's Pantry Principle" - "a place - ONE
place! - for everything, everything in its place".

What I'd like to be able to accomplish is, in table2, a field would contain
a drop down list of ID's from table1. I would select the ID and the First
Name, LastName and Status associated with that ID would be automatically
populated into table2. Each ID is unique, so there are no duplicates.

What is the purpose of Table2? How will it be used?

Note that table datasheets are *not* designed nor appropriate for data
interaction. You store data in Tables; interact with it using Forms. You can
use a Query joining some other table to Table1 to select those records that
you want to see - it is *not* necessary to have the same FirstName and
LastName stored in two different places!

John W. Vinson [MVP]

 

[Scott Nash]

Table1 was initially designed to just store employment status.

Now I've been asked to create an additional table with the employment status
along with certain requirements that go with the position.

The goal is to be able to open a form, click on the employees ID, have the
First Name, Last Name, Status go into their respective fields, enter the job
requirements in the remaining fields and save the entire record to a table.

Thanks.

 

[John W. Vinson]

Table1 was initially designed to just store employment status.

Now I've been asked to create an additional table with the employment status
along with certain requirements that go with the position.

The goal is to be able to open a form, click on the employees ID, have the
First Name, Last Name, Status go into their respective fields, enter the job
requirements in the remaining fields and save the entire record to a table.

Please reread my post.

STORING THE ENTIRE RECORD INTO A SECOND TABLE IS SIMPLY WRONG.

It's bad design, and it *will* cause you problems. If you have additional
information ("job requirements") related to an employee, store that
information in its own table and include *only* the EmployeeID as a link.

You're using a relational database. Use it relationally! If you're assuming
that you must have the name in the same table with the job requirements in
order to display or report it, *your assumption is incorrect*.

John W. Vinson [MVP]

 

[CT]

I read your post correctly the first time. I was simply answering your
questions "What is the purpose of Table2? How will it be used?"

 

[John W. Vinson]

I read your post correctly the first time. I was simply answering your
questions "What is the purpose of Table2? How will it be used?"

But you said:

The goal is to be able to open a form, click on the employees ID, have the
First Name, Last Name, Status go into their respective fields, enter the job
requirements in the remaining fields and save the entire record to a table.

I'm saying that the First Name, Last Name and Status should exist only in
Table1 (the employee data table); and that the job requirements should exist
only in the second table (the job requirements table).

To see and enter data into both tables, you can base a Form upon Table1 and a
Subform upon Table2, using the EmployeeID as the Master/Child Link Field. You
can put a Combo Box on the main form using the combo wizard to locate and
display the information from Table1 for a selected employee.


John W. Vinson [MVP]