Hi Staffer: This suggestion is from a "newbie" but perhaps you should consider keeping it to only one table and put a "check box" (or multiple check boxes) in the form and check each box as required. ie: One box would be for "applicant", another box for "non-applicant" OR better still, along with the check box for "applicant" you could have a box for "source" (ie: this person is a source) perhaps another check box for "previously employed here" etc etc. This might keep your information "clean" and make searching and/or reporting much easier than having multiple tables. Just a suggestion. Hope it helps. =Dave R.=
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of wsfstrasser Sent: Wednesday, March 28, 2007 6:30 PM To: [email protected] Subject: [ms_access] ER question: same person wears "different hats". Job applicant <==> Reference 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. [Non-text portions of this message have been removed]
