Results 1 to 4 of 4
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Select "Top 5" records in Query from one table & all related records

    Click image for larger version. 

Name:	4-26-query.PNG 
Views:	13 
Size:	15.3 KB 
ID:	45087

    Given this many to one relationship, I want to create a query that will select 5 of the records from tblRMAs where "status" is equal to "x", but show all of the related partsReturned connected to those 5 records. Currently when I use "Top Values" in the query, it limits the results to 5 records total - so if some RMAs have more than one related record, I don't get 5 RMAs selected, or I may only get some of the partsReturned selected, giving me incomplete data.

    Any ideas how to accomplish this?



    Here's the query design:
    Click image for larger version. 

Name:	qry-4-26.PNG 
Views:	13 
Size:	43.0 KB 
ID:	45088

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    create a first query with the top 5 RMA's, something like:

    qryTop5 : select top 5 RMA_nb from tblRMA4s where status = "x"

    And then link this query to table tblPartsreturned to get the related information.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Q1 is just the main table, get top 5,
    Q2 uses Q1 to join to all the other tables.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you need to create a query that returns just the top 5 records from tblRMA. Then use this new query, rather than tblRMA, in the query that you show here.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Using "Make Table" query versus "Select" query
    By Bcanfield83 in forum Database Design
    Replies: 3
    Last Post: 01-04-2019, 05:11 PM
  2. Replies: 2
    Last Post: 01-23-2017, 07:06 PM
  3. Select "X number" of most recent records
    By baseball17bucks in forum Access
    Replies: 1
    Last Post: 04-01-2014, 01:58 PM
  4. Replies: 1
    Last Post: 12-20-2013, 05:14 PM
  5. Replies: 1
    Last Post: 11-27-2012, 02:49 AM

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