title: Using surrogate keys in linking table in Access - how to show "friendly" key as well
body: <p>I am migrating a horrific database from Lotus Approach '97 to MS Access 2010. This is the first time I have used access, however I am familiar with SQL.</p>
  
  <p>I have a table of <strong>Events</strong>, and a table of <strong>Clients</strong>. These Clients are actually either <strong>Individuals</strong> or <strong>Companies</strong>. There is a joining table called <strong>ClientEvents</strong> which links the surrogate primary key of the Event to that of the Client. (i.e. There is a many to many relationship between Events and Clients).</p>
  
  <p>My problem is that whenever I try to display the ClientEvents table for a particular Event, the result is simply a list of surrogate keys. This is of no use to my client who does not recognise these surrogate keys, however as soon as I try to do a multi-join or a subquery to select the name of the individual/comapny from the relevant table, the query/form is not updateable. </p>
  
  <p>Presumably this problem is very common, as Surrogate keys must regularly be displayed next to recognisable fields from child tables?</p>
  
  <p>I have tried using a DLookup in a continuous form but this is horrifically slow. Surely this kind of design is common in access? Or am I trying to be too clever implementing a proper relational design. Is access truly capable of such designs?</p>