I have a database with the following tables:
companies (contains companyID, companyName, State, MarketingContact, BillingContact, SalesContact)
contacts (contains FName, LName, email, phone, companyName)
The "companyName" variable in the "companies" table has a one-to-many relationship to the "companyName" variable in the "contacts" table.
My supervisor wants me to create a printed contact list that looks like this:
(this illustration used dummy data)
I am able to create a report which displays the correct Marketing Contact, the correct Billing Contact, and the correct Sales Contact for each company. However, for the email variable and the phone variable it only displays the information for the first person in the company. For example: Instead of displaying the email and phone number for Mary Williams under Billing Contact, it displays her name followed by Bob's email and Bob's phone. For the second company, the same pattern happens: it displays Toy's email and Tony's phone under Mike Woods and also under Pam McDonald. This basic pattern happens with all of the companies: You see the correct names of the correct contact persons, but you only see the email and phone which belong to the first employee shown for the company (the employee in the Marketing Contact column).
This is how the report (incorrectly) displays the data:
For illustration, the incorrect matches are shown in bold italic. The email and phone for Mike Woods are incorrect; his cell displays the email and phone for Tony Adams, for example. As you can see, the email and phone values are incorrect for the "billing" and "sales" contacts (except when by coincidence that person was also the marketing contact).