Including First and Last Name fields from one table into lookup in other table

[stevekroll]

I am trying to use the first name and last name fields from one table into a lookup field - Full Name of another table. When I did this, it showed both first and last names in the lookup selection, but they were separated by a column. I could only select the first name. Should I create a query of the source table including the first and last name fields? Could I select the first and last names of the query to be used in the one field Full Name in the Lookup in the other table.

I am a new access user, so please forgive me if this has been covered before. If there is another thread that answers this question, please let me know. Thanks..Steve

[ItsMe]

A basic rule is that lookup fields in tables are a bad idea. Use a combobox control on a form and either use a table or a value list as the RowSource for the combobox control

I am not sure what you are asking about. It seems as though you want to duplicate data from one table and store it in another.

The best way would be to retrieve the first an last name from the original table, using a query. If you need to associate a record in another table you should use a unique identifier (Primary Key) from the original table and store that value in the relative table/record (using a Foreign Key field).

Create relationships (Joins) between tables using number values. Primary Key fields joined ON Foreign Key fields is a standard practice.

[stevekroll]

I have a staff table which includes first and last name fields. I have another table called Allocation which is to allocate time spent by staff members to specific programs. So the Allocation table would include ID, full name field (need first & last names), allocation % field, and program ID. Each staff member's time can be allocated to multiple program IDs. Each record would contain the unique combination of full name, allocation %, and program ID. Eventually, I would sum the allocation % field per program ID for each staff member in a query or report. So I wanted the full name field in the allocation table to be a lookup where I can select both first and last names for the full name field.

Thanks for replying. I appreciate your help.

[orange]

Further to ItsMe comments and suggestions, I'm going to point you to a link that will show you how to design your tables.
You'll have to read and work through the tutorial, but you will learn.

Good luck. Let us know how you made out. And welcome to Access and this forum!

[ItsMe]

For now, I am going to stay on point of retrieving the first and last name. It is ideal to store the first name in its own field/column and the last name in its own name/column, as you have it now.

From a combobox, from a query, display it in a form, etc. does not matter how the user interacts with the database. It is preferable to concatenate fields together.

You can create a RowSource for a combobox this way

SELECT FieldID, FirstName & " " & LastName AS FullName FROM tblEmployees

This simple SQL statement can be applied in different ways. It concatenates two fields and creates an alias with the results. The alias can be used instead of creating and storing redundant data in tables. The alias is created, held in memory, and destroyed after use.

[June7]

An alternative for the combobox RowSource is:

SELECT EmpID, LastName & ", " & FirstName AS FullName FROM tblEmployees ORDER BY LastName, FirstName;

Other combobox properties:

ControlSource: employee ID foreign key field from Allocations table
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2.0"

[stevekroll]

From a step-by-step approach, is this what I should do to achieve Full Name in the lookup table Allocation? I realize later a form will be used to enter this, but for now just want to get the info into the database quickly. Unfortunately, I don't have Access to work with until tomorrow.

Step 1 - Create a query from the employee table which includes first and last name fields.
Step 2 - In Allocation table, for Employee full name field lookup, select the just created query above.
Step 3- In RowSource of the lookup, type in Select FieldID, FirstName & " " & LastName as FullName FROM tblEmployees.

When I go to FullName in the Allocation table, I should see a list of concatenated full names to select one from?

Right now I just trying to populate this fairly small database - get the data input right, create useful queries, then create useful reports, and then do the input forms. My first stumbling block has been this so far. I am using the Access 2010, The Missing Manual to get me going.

I greatly appreciate your assistance.

[orange]

No. Read June7's post again. EmpId is FK in Allocations table.

[ItsMe]

...Right now I just trying to populate this fairly small database....

You do not need a lookup field. Save Primary Key values from the parent table into a Foreign Key field in the child table. There is not a need to duplicate data such as the FirstName field in child tables. If you tell Access what a given PK value is for a specific record, Access will be able to retrieve the necessary data for you. That is how an RDBMS works.

You will need a form to populate your tables in the correct way.

[stevekroll]

Unfortunately, foreign key isn't defined in the book I am using. Primary key is defined.

The two tables are:

Employee Staff

1) ID(primary key)
2) First Name
3) Last Name
4) Department
5) Titile

Allocation
1) ID (primary key)
2) Allocation %
3) Employee ID
4) Program ID(from Program Table)

In the Allocation table, I wanted a lookup in Employee ID to select Full Name of the employee. Since First Name and Last Name are separate fields in the Employee Table, I needed to concatenate them to form the full name in order to select it. I would then allocate a % to that full name employee associated with the program Id they are working on. An employee can be allocated to many program ids.

So given this table setup, what primary key from the Employee Table are you suggesting that gets saved into which foreign key field in the Allocation table?

I do realize I will need a form, but thought I could start with just getting these few tables populated since there isn't a lot of data and run some meaningful queries first before building the forms.

[June7]

The Employee ID field of Allocation table is the foreign key and this should be populated with ID from Program Staff table. Program ID would save the ID from Programs table.

If you set Lookup on field in the table, it will show the name but what is actually saved is the ID value. I don't set lookups in table, especially if it involves an alias. I prefer to see the actual field value.

Also, recommend no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be AllocationPct or Allocation_Pct.

[stevekroll]

Yes, EMPId is FK in Allocations Table. Thanks. Are the steps taken correct? Please see my last post.

[stevekroll]

I did use the ID field from Employee Table(program staff was wrong) as the link(foreign key) to the Allocation Table Employee Id field. But will that step alone populate the Employee ID with their full name from the two separate fields of first and last name from the Employee Table? The person who will be inputting the Allocation Table needs to be able to see the full name in order to make sure they are assigning the right person.

[orange]

Steve,

Unfortunately, foreign key isn't defined in the book I am using. Primary key is defined.

Work through this tutorial --- get a different book OR make use of Google/Bing

[June7]

You still miss the point. This will not (and should not) populate Allocations table with the employee name, only the ID. Users will see the full name in the combobox RowSource as well as in the table field because of the lookup but the actual value saved is the ID.