I'm creating a database which records details of many teams of people. Each team has one record, one of the fields within which is a lookup field. The Lookup allows the selection of the team's supervisor from a "Supervisors" table.

The supervisors table contains, amongst other things, the following fields: ID, Title, First Name, Surname.

When creating the lookup field, I chose to hide the key column, because it contains a random autonumber which is meaningless to the user. So, when the lookup is now used, the First Name value is displayed in the Teams table (though it must actually be storing the key value somewhere, because supervisors with the same First Name are not causing problems).

The problem with this is that the First Name alone is not enough to identify the supervisor. I need the Teams table to show the Supervisor's First Name and Last Name (preferably in one column and separated by a space).

I've tried to do this by creating a Query of the supervisors table with a field that strung together the first name and last name. However, when I set the lookup to find values from the query, it doesn't give me the option of hiding the key column, so that only value I can return to the Teams table is the key value, or the name (but then I can't have multiple people with the same name).

Can anyone help me achieve what I'm trying to do?

Thanks,

Gary

I do this kind of thing like this:

Use the combo box wizard to develop the combo box. Put the ID, FirstName, and LastName fields in the combo box. Hide the key column as it wants to do. Complete the Wizard as normal.

Then when the Wizard is finished, get the properties of the combo box and modify its Row Source property to join together the FirstName and LastName fields as you are doing in the query:

Modify this:

SELECT TableName.EmployeeID, TableName.FirstName, TableName.LastName FROM TableName

so it becomes this:

SELECT TableName.EmployeeID, TableName.FirstName & " " & TableName.LastName As FullName FROM TableName

Other fields can be included if you wish.

I do it similarly to Frank, only I do it like this:

SELECT TableName.EmployeeID, TableName.LasttName & ", " & TableName.FirstName As FullName FROM TableName ORDER BY Lastname, Firstname;

This way you display the fullname but in a more logical order.

Its SOP to store the ID field but to hide it. A combobox will display the first non-zero width column once a slection has been made.

Another suggestion, I would NOT have a separate table for supervisors. Supervisors are employees also and may have their own supervisors. So they should be in one Employee table. Just add a field to that table that identifies them as a team supervisor. So your RowSource now becomes:

SELECT TableName.EmployeeID, TableName.LasttName & ", " & TableName.FirstName As FullName
FROM TableName
WHERE EmployeeTypeID = 1
ORDER BY Lastname, Firstname;

You would have a lookup table of employee types where one type would be Team Supervisor. In my example the ID for that type would be 1.

Thanks Frank & Scott, this has worked perfectly.

Scott - I can't include the employees in the same table as the teams data because it's slightly more complicated than the description I gave above in that the teams are all in different organisations from the supervisors!

Continuing on a similar idea, I now have another problem with which perhaps you can help?

The address data is split into separate fields for each line. How can I create a form that displays a large text box with the full address in, separated with line breaks after each field? I.e. convert:

Field: Organisation
Field: Buidling Number
Field: Street Address
Field: Town/City
Field: County
Field: Postcode

into

Organisation
Building Number
Street Address
Town/City
County Postcode

Thanks in advance if you can help with this.

Gary

In a query, use concatenation to join the various fields into one calculated field. You can put the line breaks in as well. Then base a form on that query.

Expr1: [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) etc.

Then put a control on the form that is bound to Expr1.

Edited by: fkegley on Thu Jun 1 13:02:33 EDT 2006.

Thanks Frank & Scott for your responses above. Exactly what I cam to UA looking for today. I was able to get my lookup working right away.

My question about the lookup is its use on the form vs. in the field properties at the table level. If you apply the lookup at the table level, then you would not have to re-create it for more than one form. Is there any danger of diong so?

Frank,

Many thanks for your advice which does exactly what I want in combining the address into a single text box. The reason I wanted to do this is that I frequently want to make a single address label with addresses from my database, and need to be able to copy the full address to paste it into my label-printer software. This will make it much quicker than copying it field by field.

However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.

When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.

How can I achieve this within the code you provided?

Gary

Anyone?

I assume I need to place the contatenation code Frank gave me for each field with an iff statement so that if the cell is blank, the field and following space and linebreak are not inserted? I'm just not exactly sure how to write this!

Gary

I've managed to figure this out and successfully acheived what I wanted using the following code:

Expr1: IIf(IsNull([Title]), "", [Title] & " ") & IIf(IsNull([First]), "", [First] & " ") & IIf(IsNull([Surname]), "", [Surname] & Chr(13) & Chr(10)) & IIf(IsNull([Organisation]), "", [Organisation] & Chr(13) & Chr(10)) & IIf(IsNull([Address1]), "", [Address1] & Chr(13) & Chr(10)) & IIf(IsNull([Address2]), "", [Address2] & Chr(13) & Chr(10)) & IIf(IsNull([Address3]), "", [Address3] & " ") & IIf(IsNull([Postcode]), "", [Postcode])

Thought I'd post it back here in case it's of use to anyone else who might read this thread in the future!

That is exactly what I would have advised.

Very good, glad you got it, thanks for posting the solution.

Cheers Frank. Thanks for giving me a starting point.