Results 1 to 2 of 2
  1. #1
    GREG30043 is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2014
    Posts
    5

    Contact list with multiple fields from multiple people per row, multiple people per company

    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:

    Click image for larger version. 

Name:	report-display.JPG 
Views:	10 
Size:	87.4 KB 
ID:	17680
    (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:
    Click image for larger version. 

Name:	incorrectr-displauy.JPG 
Views:	10 
Size:	88.0 KB 
ID:	17682
    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).

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post queries for analysis.

    The contact fields in companies table should have the ContactID. Pull the contacts table into the query 3 times and link 1 to each contact field from companies table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-11-2013, 10:05 AM
  2. Replies: 7
    Last Post: 06-20-2013, 12:09 PM
  3. Replies: 23
    Last Post: 12-06-2011, 09:18 AM
  4. Display list of people waiting
    By foxtet in forum Forms
    Replies: 1
    Last Post: 06-21-2011, 12:34 PM
  5. Replies: 0
    Last Post: 12-28-2009, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums