Results 1 to 3 of 3
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Queries Joining 3 tables At Once

    Not a good title, but I didn't have anything better. Here's the fictional situation. I have Salesmen (table) who are linked to Customers (table, one-to-many) and Prospects (table, one-to-many). I want a query with three columns... Salesman, Customer, Prospect. If I use Salesman as the Primary table and JOIN it to the other two tables, I get a huge number of rows. It lists the Salesman, then the Customer, then ALL the Prospects... then the next Customer for the Salesman, and ALL the Prospect, etc. I want to query to give me the first Salesman, the first Customer, and the first Prospect. On the next row, I want the same Salesman, the next Customer (if there is one, otherwise blank), and the next Prospect (if there is one, otherwise blank). I want the same Salesman to appear on each row until BOTH the linked Customers and Prospects are listed... then it should go to the next Salesman and do the same thing. Any help would be appreciated. Thanks, Eddie

  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
    Use a report with subreport(s).

    Prospects are not associated with Customers. So there is no way to order 'next' customer and 'next' prospect. Can list all customers for a salesperson and all prospects for a salesperson, can't 'interweave' the customers and prospects with direct join of salesperson to the other two tables and prevent the cartesian joining that results in the 'duplication'. Could have customers and prospects in separate subforms side-by-side.

    A union of the customers and prospects then joining that union to salesperson might get you a single dataset like you want.

    SELECT ID, LastName, FirstName, SalespersonID, "Customer" As Category FROM Customers
    UNION SELECT ID, LastName, FirstName, SalespersonID, "Prospect" FROM Prospects;

    Now join that UNION query to Salespersons table and order by SalespersonID, ID fields.

    (Note the politically correct, non-gender specific object naming)
    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.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    Ended up writing some VBA code to make it happen... Thanks for the suggestions... Eddie

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

Similar Threads

  1. Trouble Joining Two Queries
    By ianclark2992 in forum Queries
    Replies: 2
    Last Post: 06-28-2013, 08:18 AM
  2. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-22-2012, 12:21 AM
  3. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  4. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  5. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 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