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

[stevekroll]

I now understand that only the ID from the Employee Staff table is stored in the Employee ID field of the Allocations Table. Where exactly is the combobox RowSource located? Which table field are you referring to where the full name will be displayed?

[June7]

You can set field in table to behave like a combobox - a control with a dropdown list. With Allocations table in Design view, select the EmployeeID field and click on the Lookup tab at bottom of the designer. Select combobox and set the RowSource property. The EmployeeID field will then display the related full name instead of the actual content of ID.

However, the recommendation is to NOT do this in table. Build a form bound to table and create combobox control on form.

[orange]

More combo box info here with examples.

[stevekroll]

I think all of you have encouraged/warned me to build a form and create a combobox control on the form. I will do this instead of doing this in a table. Can you tell me the reason why doing it directly in a table is not a good idea? What problems does it present to the database?

I really appreciate this forum. I want to thank everyone for responding to my question and trying to point me in the right direction.

[orange]

Have you tried Google or Bing to do any research?
Here's one link to Access MVPs site that has related info.

[stevekroll]

I see that I could accomplish this through a query. I don't need the full name in the Allocation Table. Meaning I would just define a relationship between the ID field of the Employee Staff table and the Employee ID field of the Allocation table. In Table Design View, when I initially define Employee ID field, what do I type in? Do I do a Lookup just selecting the ID field from the Employee Staff Table? When I create the input form for Allocation, what would be entered in the Employee ID field?

[ItsMe]

You will need a unique identifier to create joins. This would be the primary key. It will join to the foreign key field in another table. In most cases, an autonumber data type is appropriate for the PK's and the FK's would then be, Long Integer.

The Employee ID field may be important to the user. The user may need to see the Employee ID field and understand its value because it may be relevant to daily operations. If this is the case, you may consider using a second field as the PK, maybe naming it EmpPK, and use the Autonumber data type. Regardless, you should have an autonumber as PK and long integer as FK. You should create the joins using these fields.

With the appropriate PK/FK structure, most all of your concerns about First Name/Last Name will sort themselves out.

[June7]

As already stated, don't build lookup in table. Just create the fields. Build combobox on form. Set the same properties that would be set in table lookup.

Here is how to set up a multi-column combobox http://www.datapigtechnologies.com/f...combobox3.html. Do this on form bound to Allocation table.

The combobox RowSource could be:

SELECT EmpID, LastName & ", " & FirstName AS EmpName FROM Employees;

[stevekroll]

The ID field of the Employee Staff table will be the primary key and will be joined to the Employee ID(foreign Key). ID of Employee Staff is an Autonumber data type and Employee ID will be long integer. The joins will be on these two fields since it will be defined as a relationship.

[stevekroll]

Ok. I will just create the field Employee ID in the Allocation Table as a number/long integer? Just define it like that? Then go to Forms and create the combo box on form. Do I have to create the combobox in Design View or is Form View okay? Where do I access the combobox?

[orange]

Design view, in the toolbox.

[stevekroll]

I wanted to thank everyone for helping me. I was able to successfully implement the combobox in a form. Then I used the form to input sample records to populate the table and then created a query. I also found the pivot table very helpful for what I am doing.

I have found one very strange thing though. Since I am dealing in two decimal entries, I have formatted it as single(field size), fixed and two decimal places. I am able to enter it fine in the input form, but in the certain fields in the crosstab query it displays the value such as .24 from the form as .239990000007896(15 decimal places) instead of .24. I noticed in the pivot table when I hold the cursor over the number correctly displayed as .24 it highlights as.239990000007896 as if this is what is being stored. Totals add up okay in queries and reports based off the queries, but the query details section all values were 15 digits except .25. Does anyone know what is going on here? How can I fix this? I have made sure that every property for each cell is fixed and two decimal places.

[orange]

I don't know what's in your cross tab, but this sort of thing is often resolved with

Round(YourFieldName,2) but read this info before accepting this as a solution

another option may be to format yourField as "Currency"