Normalizing Address Information...

THE LAYOUT:


I have two tables: "Applicant_T" and "StreetSuffix_T"





The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:





[Applicant_T]


CurrSuffix


PrevSuffix


EmpSuffix





The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:





[StreetSuffix_T]


SuffixID <-----this is the primary key


Name





For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.








THE PROBLEM:


I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?