Results 1 to 9 of 9
  1. #1
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41

    LIMITING Query results from multiple tables

    How do I get this query to show only one result if the Contact_ID may have more than one customer, associated process or task associated with it? I have tried adding DISTINCTROW to it but it doesn't seem to have any affect. I have also tried TOP 1 but this doesn't do what I need it to either.



    SELECT Main_Table.Contact_ID, Main_Table.Full_Name, Main_Table.Contact_type, Customer_Table.Customer, Team_Table.Team, Associated_Process_Table.Associated_Process, Task_Table.Task
    FROM (((Main_Table LEFT JOIN Customer_Table ON Main_Table.Contact_ID = Customer_Table.Contact_ID) LEFT JOIN Associated_Process_Table ON Main_Table.Contact_ID = Associated_Process_Table.Contact_ID) LEFT JOIN Task_Table ON Main_Table.Contact_ID = Task_Table.Contact_ID) LEFT JOIN Team_Table ON Main_Table.Contact_ID = Team_Table.Contact_ID;


    Click image for larger version. 

Name:	database.JPG 
Views:	13 
Size:	71.5 KB 
ID:	29466

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    It depends on the fields you show....
    Using this same query you show, if you put only 2 fields in the query: ID,CUSTOMER,
    then you could get only 1 record.
    The more fields you bring down, the more records you will get EVEN tho it is still distinct.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    or change the query to a group by query and change group by to first or last or max or min for fields relating to your customer, associated process or task associated tables.

    Note that this does not mean the values displayed relating to your customer, associated process or task tables will be related to each other - from the data you have provided associated processes does not to relate to a customer anyway for example

  4. #4
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    HI I have tried using the group by on the fields with multiple entries it does work on the query, however it brings up another issue - the attached form is one users can use to search by task/process/customer but if the selection they make is the 2nd one assigned to the customer ID then it is not included in the results - obviously the limiter of group by first coming into play here - is there a way around this? I want to show results based on clicking the associated buttons but only show one contact ID once in the results list despite the fact they have multiple tasks/process/customers assigned to them. I need the search to work whichever selection is made regardless of whether it is the first or second or in some cases third task/process/customer assigned to one Contact ID....

    Click image for larger version. 

Name:	Search form.PNG 
Views:	10 
Size:	17.1 KB 
ID:	29468

    This is the search form and this is the macro behind the 'Search by Task' button - the Search_results_form is based on the query that I am asking about.

    Click image for larger version. 

Name:	macro .JPG 
Views:	10 
Size:	17.6 KB 
ID:	29469

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    What does each table represent in "business" (simple, plain English) terms?
    How are these tables related--it isn't clear from your jpg?

    It seems that
    a Process may have 1 or more Tasks

    More info needed.

  6. #6
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    This is basically a contacts database so I have

    Main table - this contains name, type of contact (internal, external etc) email address, contact number and company name - there is a unique identifier of contact ID set up on this table

    then I have the following tables which are all related to the main table by the contact ID field
    Task table - Tasks that each contact is responsible for - each contact may have more than one task
    Associated process table - Internal processes that the contact may be associated with - each contact may be associated with more than one process
    Comments table - comments about the contact - there may be more than one comment per contact
    Customer table - customers that the contact is responsible for- each contact may have more than one customer and each customer may have more than one contact
    Team table - the team that the contact belongs to - each contact should only have one team
    group email address table - any group email address associated with the contact - each contact may have more than one group email address.
    I also have a process allowed table which allows me to restrict the selection of process to those specified but this is not related to main table.

    this is the relationship set up
    Click image for larger version. 

Name:	relationship .JPG 
Views:	10 
Size:	71.9 KB 
ID:	29471
    Last edited by moneypennie21; 07-17-2017 at 07:11 AM. Reason: incorrect attachement

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Here's a possible draft model based on your comments. It may not meet your needs/requirements.
    You know your situation better than readers. It may be useful.
    Good luck.
    Click image for larger version. 

Name:	ContactTeamTaskGroupDraftModel.PNG 
Views:	9 
Size:	46.2 KB 
ID:	29473

  8. #8
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    Hi Orange,
    thanks for the help but I am quite new to this so not sure what your solution means. I really need to understand what is wrong with the relationship set up in my example and therefore what the difference is in yours (what is the red/green/blue significance) to really get my head around this. are you able to explain any of this in a simple way?

    Thanks

    Jen

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    I read your comments and working from those attempted to draw a picture of your entities and relationships. I did this with a modeling tool. The red identifies PK (primary key), green (Foreign key). Blue represents a composite PK in junction table. I do not know the details of your business, so this is strictly a draft based on your post/comments.

    There may be nothing wrong with your relationships. You know your business. I don't. Test your model with some sample data.

    I recommend you always start with a clear description of the business issue/opportunity; then build a model (pencil and paper is fine) then test the model with some sample scenarios (mock up some outputs required, some sample data/input) and makes sure the model structure can support the scenarios. Adjust the pieces as necessary, and once you confirm the model supports your needs, use it to build your database.

    Work through 1 or 2 (or more ) of these tutorials at RogersAccessLibrary. You will learn a process by working through the tutorials.
    For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials.
    Class info system
    Catering Business
    Widgets


    Good luck.

    Check out this link for more info on database design and planning.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-07-2015, 11:23 PM
  2. Replies: 24
    Last Post: 04-08-2015, 02:59 PM
  3. Limiting Query Results to Unique Values
    By orangeman2003 in forum Queries
    Replies: 2
    Last Post: 12-25-2013, 01:08 PM
  4. Replies: 12
    Last Post: 12-17-2010, 05:35 PM
  5. Limiting the results displayed in a report
    By musicalogist in forum Reports
    Replies: 1
    Last Post: 06-10-2010, 04:44 PM

Tags for this Thread

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