Results 1 to 6 of 6
  1. #1
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17

    Post finding multiple records

    I have a table/tables that look like something like bellow and it has couple thousand of records.

    ID protocol
    1 10


    2 10
    2 12
    3 11
    4 11
    5 10
    3 12
    3 10
    6 11
    6 12
    6 13


    ... ...


    I can easily find all the IDs that belong to either protocol 10 or protocol 11 or protocol 12... etc.
    But I need to know if a certain IDs are not only into protocol 10 but also into some other protocols (in this example, 3 is also involed in protocol 12, ID 6 is in protocols 11, 12 and 13 etc),
    and so on for all other ID. To be more precise, I want to obtain the ID that are not only in, let's say protocol 10, but also in other protocols and to list them all.


    Thanks for your help guys.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    select [protocol] from table where [id]=3

    or use a form with a combo box to select the ID, then the query is:
    select [protocol] from table where [id]=forms!myForm!cboID



  3. #3
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17
    Thanks, the first part would only list one by one ID, and that's not what i need, as I said, I have thousands of IDs.
    The second part I simply do not understand :-). Sorry... :-) Thanks anyway. Cheers! :-)

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you mean, what ID have multiple, then coun them
    select ID, Count(protocol) from table

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ranman's first suggest was for query to reference a combobox on form to provide a filter parameter. But that still shows only one ID group.

    If you just want a count then do an aggregate query.

    If you want to see which protocols are associated with which IDs (or vice versa), then sort records as appropriate. Build a report that sorts and groups.

    Other approaches will likely require VBA code. One is to concatenate the related data into a single string. Review http://allenbrowne.com/func-concat.html

    So you can have result like:
    ID Protocols
    1 10
    2 10, 12

    or this
    Protocol IDs
    10 1, 2, 3, 5
    11 3, 4, 6
    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.

  6. #6
    Batta2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    17
    Thanks guys. I'll try those too. I know I can do it way around, like creating a table that has only IDs with the protocol of interest (for example protocol 3) then join these two tables and find if there are IDs related to some other protocols, but I wanted more elegant way than mine :-). VB coding seems kind of very powerful, it's a pity I am not good in that at all :-(.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  2. finding new records for a given period
    By zburns in forum Queries
    Replies: 3
    Last Post: 04-12-2015, 06:17 PM
  3. Replies: 3
    Last Post: 02-22-2013, 11:34 PM
  4. Finding Records that are not there!
    By TrudyD1474 in forum Queries
    Replies: 2
    Last Post: 06-18-2010, 04:41 PM
  5. finding records in a database....
    By softspoken in forum Forms
    Replies: 1
    Last Post: 04-23-2010, 11:17 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