Charlotte Foust
charlotte.foust at gmail.com
Wed May 18 11:16:18 CDT 2011
Do you actually have any names associated with those aliases? If not, that could be the problem. Charlotte Foust On Wed, May 18, 2011 at 12:39 AM, Gustav Brock <Gustav at cactus.dk> wrote: > Hi Jürgen > > Weird. > Try adding DISTINCT: > > SELECT DISTINCT tblEmployee.EmployeeID, .. > > /gustav > > >>>> jwelz at hotmail.com 17-05-2011 21:56 >>> > > I've devised a method to allow users to select a person's record by an alias first name in a lookup combo and there appear to be some issues. For example, I have a fellow known as Willie whose payroll name is Guillermo and I need my users to be able to look up the user in a search combo. Choosing either Willie xxx or Guillermo xxx navigates the form to display the Guillermo record. Aliases include common misspellings such as Jurgen and Juergen for Jürgen. Alias names appear in a continuous sub form on the main form record. > > Assume a master record in tblEmployee with an autonumber primary key, EmployeeID and text FirstName and LastName fields. > > Assume an alias table that contains a foreign key pointing to the Employee record and a text FirstNameAlias field. > > The alias names are unioned to the employee names in the lookup combo. The combo displays full names being the FirstName & ' ' & LastName concatenation and there are options to load LastName & ' ' & FirstName and to include only active, only inactive or both records as well as regional filters. > > The query that I wish to union appears: > > SELECT tblEmployee.EmployeeID, tblEmployeeAlias.AliasFirstName & ' ' & tblEmployee.LastName AS Employee > FROM tblEmployeeAlias INNER JOIN tblEmployee ON tblEmployeeAlias.EmployeeID = tblEmployee.EmployeeID > > I have 31 alias names in the table. 28 of them work. 3 don't in that the concatenation of the LastName doesn't happen. > > If I add a column to the select statement: [LastName] & ' ' & [tblEmployeeAlias].[FirstName] AS Expr1, the LastNames appear. If the select statement includes a column for LastName alone, it appears in the query so there is no problem with missing or null data and the join functions. We know it functions as the 3 alias names appear and a join failure would exclude those records. > > AliasFirstNames that don't show a concatenated last name are Rob, Rick and Mitch. The last name will not appear in the concatenation of the Alias table first name with primary record last name in the 3 records. I don't commonly do display concatenations of fields from different tables but am baffled why some work and some don't. There are no names with apostrophes. Common names like Mke and Ron work as do unusual name like mine which is commonly misspelled. It seems to make no difference if the first or last name is unique. If I reverse the order of the names in the concatenation, the Last Names show. > > Ciao > Jürgen Welz > Edmonton, Alberta > jwelz at hotmail.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >