title: MS Access - how to create a label on a form that populates data from another table
body: <p>I have a fairly simple database that I inherited.  For the purposes of this question, there are two tables:
  Mastertable and Providertable.</p>
  
  <p>Mastertable references Providertable thruogh provid, which is a FK to Providertable PK (provid).</p>
  
  <p>So it looks like this:</p>
  
  <p>Mastertable:</p>
  
  <p>acct (PK)
  (other fields)
  provid (FK)</p>
  
  <p>Providertable</p>
  
  <p>provid (PK)
  provname
  provspecialty</p>
  
  <p>Simple right?  However, the Mastertable!provid field is actually a lookup table which displays Providertable!provname but stores provid.  There is a form the users use to populate the Mastertable, and it has this lookup field shown.  </p>
  
  <p>The users now want to show the provider specialty based on what they select as the provid.  I can't figure this out to save my life.  I'm pretty well versed in SQL, having written many stored procedures and created a few db apps using .NET, but this is quite challenging.  I tried creating a lookup field called provspeciality, but that's not what they want.  I tried changing the "OnUpdate" event for the lookup field to point the Provider Specialty label to the right thing.</p>
  
  <p>Right now, I can't even get a simple select going that joins the two tables since they are using this lookup field as the FK and Access I guess can't understand it.  Any help appreciated.</p>