Hi there, You can have all the people in one table and have that table in a many-to-many relationship with itself:
tblPeople --------- [ID],[LastName],[FirstName] 101,Adams,Alice 102,Bernstein,Bob 103,Comden,Cassandra 104,Davidson,Donald tblReferences ------------- [ID],[ReferenceSource],[ReferredPerson] 1,101,102 2,101,103 3,102,104 Alice referred both Bob and Cassandra, while Bob referred Donald. You can easily count the number of times anyone's ID number appears in each column. You can also add columns to tblReferences to store other information about the reference. Hope this helps. Good luck! Peter Hoogenboom --- In [email protected], "wsfstrasser" <[EMAIL PROTECTED]> wrote: > > I know same person should not be entered twice, but I don't know what > else to do. > I have a little job search database. > Applicants are in one table. > > Also have to keep track (group and do counts on) people who act as > "sources" for job applicants (source = person who suggests I make > contact with an individual who might apply for a job). > Similarly have to keep track of "references" for job applicants > (she/he worked for me). > > Frequently individuals "wear different hats": > 1) apply for several jobs > 2) and/or suggest people who I should contact as job applicant > 3) any/or act as reference for job applicant > > Reports include who was best source? who was used as a reference the > most? which applicant was suggested the most by sources? how many > and who were the references for the applicants for job positions? > > My solution was to create a tblApplicants and a tblNon-Applicants > related in a "many:many" relationship. > > Records of individuals in tblNon-Applicants include the following 2 > check boxes. Individuals recorded in tblNon-Applicants can act as > either a source or act as a reference or act as BOTH, for multiple job > applicants. > > Reporting works fine, but any change in an individual's phone number > or address must be done in both tables: tblApplicants and > tblNon-Applicants. > > Any ideas? > Database is working with the two tables but, although changes and > searching for discrepancies for the same individual recorded in both > tables is automated, the redundant data makes me very uncomfortable. > Thanks in advance. >
