Results 1 to 5 of 5
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    How to query table to pull the list of id's that were tested for a specific value just one time


    My table has 2 to 3 test results for each id. I want to get the id that have only one record where Result='Repeat'
    id Results TestNo
    123 repeat Test 1
    125 Repeat Test 1
    123 Negative Test 2
    123 Negative Test 3
    127 Positive Test 1
    128 Repeat Test 1

    In the above case I want to pull 125 and 128 records

    I want to pull the list of all the Repeat results for testing which were not test again.
    Not sure if my question is clear.
    Will the Self query work?

    Also is there a a way to query the table to get row counts on the spot

    thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try:

    SELECT * FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE Result<>"Repeat");

    What do you mean by 'row counts'? Show an example.
    Last edited by June7; 02-29-2020 at 10:12 PM.
    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
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you that query worked.
    Regarding row counts..
    id Results TestNo
    123 repeat Test 1
    125 Repeat Test 1
    123 Negative Test 2
    123 Negative Test 3
    127 Positive Test 1
    128 Repeat Test 1

    Select Id, count(*) as Total from table1 group by id;

    Is there a way to use Total function directly in the DATA sheet view to calculate row counts(Similar to Column counts) instead of going into SQL view and adding counts function.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If I understand correctly, no. Could build a report and use its Sorting & Grouping features with aggregate calcs in textboxes. This allows displaying detail data as well as summary calcs.

    What do you mean by Column counts?
    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.

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    By column counts I mean..

    1. On the Home tab, in the Records group, click Totals.

      A new Total row appears below the last row of data in your datasheet.

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

Similar Threads

  1. Replies: 15
    Last Post: 01-14-2020, 12:43 AM
  2. Replies: 5
    Last Post: 07-11-2019, 04:09 PM
  3. Use of dropdowns to pull specific Query fields
    By dbright4 in forum Reports
    Replies: 4
    Last Post: 03-31-2016, 08:59 AM
  4. Replies: 17
    Last Post: 11-05-2013, 04:19 AM
  5. Replies: 1
    Last Post: 08-01-2012, 12:50 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