lookup field from linked table

[gadgettools]

I have a personal data table that I linked to a forms table(refering to a
form that I can hand to people not an Access Form). What I am trying to do
is pull three field names (last name, first name, and position) and make a
list of names with those three fields on a forms page. In my design form, I
created the combo box and pulled all three fields (last name, first name, and
position) from the personal data table. When I open the form and use the
combo box all three fields show but only the last name stays. I'm not sure if
I am going about it the right way.

 

[John W. Vinson]

I have a personal data table that I linked to a forms table(refering to a
form that I can hand to people not an Access Form). What I am trying to do
is pull three field names (last name, first name, and position) and make a
list of names with those three fields on a forms page.

That's almost certainly neither necessary nor appropriate. Storing data -
names - redundantly in the Forms table is not a good idea! Instead, store only
the PersonID (the primary key of the personal data table, whatever it's
called) in the forms table, and use a query to look up the name as needed.

In my design form, I
created the combo box and pulled all three fields (last name, first name, and
position) from the personal data table. When I open the form and use the
combo box all three fields show but only the last name stays. I'm not sure if
I am going about it the right way.

What are the fields in your two tables? How are the tables related? Are you
allowing for the possibility that you might have two people named Jim Smith,
or are you incorrectly assuming that names - perhaps even last names - are
unique?

John W. Vinson [MVP]

 

[gadgettools]

An employee ID will be the primary key. What im trying to do is make a form
that shows all the people that attended a training class. The form will give
the type of traing and who gave it but also include a list of around 60
names. Not everyone will from the individual data profile (IDP) database
will be inserted just the ones that atteded. I also only want a few pieces
of info from the IDP which includes Emp. ID #. This will allow me to keep
track of who did training by paper and on the computer.

 

[John W. Vinson]

An employee ID will be the primary key.

The primary key of the Employee table, sure; but it cannot possibly be the
primary key of the training table, unless you want each employee to be limited
to one and only one training opportunity.

What im trying to do is make a form
that shows all the people that attended a training class. The form will give
the type of traing and who gave it but also include a list of around 60
names. Not everyone will from the individual data profile (IDP) database
will be inserted just the ones that atteded. I also only want a few pieces
of info from the IDP which includes Emp. ID #. This will allow me to keep
track of who did training by paper and on the computer.

If you're starting the design of your tables with a Form you're going about it
backwards. The Tables are fundamental; get the tables designed correctly first
and THEN worry about the form.

As suggested: you need three tables. First would be your Employees table, Emp
ID# as the primary key (though don't use # in your fieldname, it's a date
delimiter).

Second would be a smallish table listing what training classes are available.
This table would have a TrainingID as its primary key.

Third would be an Attendance table, listing who attended what class. This
table would have fields for EmpID, TrainingID, and other fields about this
employee's involvement with this class - e.g. date attended,
satisfactory/unsatisfactory performance, etc.

For your Form you could use a form based on the IDP table, with a Subform
based on Attendance. This subform would have a combo box bound to the
TrainingID field in Attendance, displaying the course name, along with
controls to display the other fields from the attendance table.

John W. Vinson [MVP]