[AccessD] People Alias Names

Jurgen Welz jwelz at hotmail.com
Wed May 18 15:13:26 CDT 2011


Charlotte:  Yes there are last names for the records.  Reversing the concatenations, the last name shows up correctly.  Also, adding the last name field to the query as an additional column displays the last name.  There are records in the main table associated with the alias table names since they show up as records on an equal join.  The problem arises somewhere from the fact that one concatenated query field concatenates data from fields in different tables.  The oddest thing, that also answers your question, is that if I display the last name first in the concatenation, all the names in the search combo display in their entirety.  That is, if I select tbl1.LastName & ' ' & tbl2.Alias as one field and tbl2.Alias & ' ' & tbl1LastName as another field in the same query, the output is missing the last name in 3 of the concatenated records where the first name appears first, but it shows in all of the records where the last name appears first.  Simply reversing the order of the concatenated fields changes the displayed output.  What is also odd is that the space concatenated between the names also fails to appear.  That is to say, the name Rick, where it appears, has a length of 4 and Rob as 3.  There is an example of Rick where the contatenatation succeeds but there is no other example of Mitch (or the full proper name).
 
SELECT tblEmployee.EmployeeID, tblEmployeeAlias.FirstNameAlias & ' ' & tblEmployee.LastName AS Employee, [LastName] & ' ' & [tblEmployeeAlias].[FirstNameAlias] AS Expr1
FROM tblEmployeeAlias INNER JOIN tblEmployee ON tblEmployeeAlias.EmployeeID = tblEmployee.EmployeeID

yeilds
 
12     Brad Card    Card Brad
432   Jim Jaybo    Jaybo Jim
2344  Rick            Mackie Rick
3444  Rob            van der Hout Rob
4455  Ron Kay      Kay Ron
5445  Mitch          Blist Mitch
5987  Bob Kin       Kin Bob
 
I've also reversed the Inner Join order and sides of the comparison operator in the join to no avail.  There is no numerical ID or first or last name alphabetical sequence in the success/failure to display the last name side of the concatenation in the 2nd column.
 
What I get is 31 records returned.  28 records have the full first and last names in the first column but 3 with first name only.  In the 3rd column all 31 records have both a last name and first name.  These 31 records are unioned with the records from the tblEmployee to fill the employee search combo.  I have various options to display regional, active, inactive and other types of employees and to show them by last name or by first name.  Most people prefer the first name first approach and that is the one that doesn't consistently work.  When I display last name first, I also concatenate a comma with the space to make it obvious and I change the combo back color to indicate that the not in list event will not fire in last name first mode.
 
Gustav:  Distinct did not work.
 
I can fix this by adding the last name field to the Alias table and storing it for each alias entered.  This denormalization would simplify the union query that fills the look up combo but would require me to maintain the last name in the alias table with edits to the main table last name.  I lock the textbox displaying the names and require a click on an edit button for each field to be edited so I can control this, though I'd rather not have to code a fix where none should be required.
 
I plan to extend the alias concept to other single field 'name' tables to accomodate things like company names (PCL = P.C.L. = P C L = P. C. L.) and equipment component names (Side Bracket = Outrigger = Out rigger = Hanger) but am perplexed by the odd inconsistent behavior of the query and am leery of future complications.
 
It's as though I evaluated the following lines in the immediate window and all evaluated to true except the 3rd:
 
?1=1
?2=2
?3=3
?4=4
 
One thing I hadn't mentioned is that I am using an MSAccess ODBC connection to tables in SQLServer 2005.
 
At this point, I'm tempted to run a make table query to bring the data back to native access and populate all the data.  I'll also try adding some additional aliases to the misbehaving records in situ to determine whether they continue to fail.

Ciao 
Jürgen Welz 
Edmonton, Alberta 
jwelz at hotmail.com


 
> Date: Wed, 18 May 2011 09:16:18 -0700
> From: charlotte.foust at gmail.com
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] People Alias Names
> 
> 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 		 	   		  


More information about the AccessD mailing list