MS Access Lookup field

Results 1 to 2 of 2

Thread: MS Access Lookup field

  1. #1
    Join Date
    Dec 1969

    Default MS Access Lookup field

    Hi everyone. I&#039;m not guru in access, so bear with me, please.<BR><BR><BR>I have a table that stores personal information about users who sign up. They are put into groups according to a sector that they work in.<BR><BR>The *name* of the sector they wish to apply for is in this same table.<BR><BR>In another table, I have a matching sector name along with an ID field that assigns it a unique number.<BR><BR>I want to add another field to the original table that has the ID field of the sector in it, but I do not want to manually enter it. Rather, I would like to be able to query for that number based on the sectors name, and have it automatically inserted into that field.<BR><BR><BR>Does that make sense? It does to me, but perhaps not to all of you.

  2. #2
    Join Date
    Dec 1969

    Default RE: MS Access Lookup field

    I take it you don&#039;t have much experience designing databases, no?<BR><BR>The way you have it is the wrong way around. Instead of this:<BR>PersonalData<BR>EmployeeName<BR>SectorName<BR><BR>Sectors<BR>SectorName<BR>SectorID<BR><BR>You REALLY should have THIS:<BR>PersonalData<BR>EmployeeName<BR>SectorID<BR><BR>Sectors<BR>SectorName<BR>SectorID<BR><BR>Notice how you store the ID, not the sector NAME in the PersonalData table. You CAN use names as "foreign keys", but it&#039;s normally quite bad practise. What happens if you rename the "Finance" sector to "Finance and Debt Collection"? You have to now go through your PersonalData table and update all the rows....<BR>If it was done the other way around, you&#039;d update ONE row in the Sectors table.<BR><BR>If you want to be able to pull out the personal data and associated sector, you&#039;d simply do a JOIN between the two tables...<BR><BR>Craig.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts