Results 1 to 2 of 2
  1. #1
    TJ1010F is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2015
    Posts
    5

    Return all records of ID when one record meets criteria

    Hi,



    New to this forum and access so apologies if this is a very basic question.

    I have 3 tables lets call them Identifier, Left and Right (referring to Left and Right arms)

    Identifier contains the ID's and age, DOB, etc of all the patients in my database
    Left and Right contain the ID and specific measurements of each arm. There can be numerous entries within Left or Right for each ID because each patient can have measurements taken at different times.

    I need it so that when I run a query for a specific measurement within the Table Right, ie Circumference of wrist < 10 cm, not only does it return the record of all the patient's that meet that criteria, but also all other records in Right and Left of that patient who has met the criteria, even if their other visits do not necessarily meet the criteria.

    Table: Right Arm
    Patient ID Visit ID Wrist Circumference
    1 1 9
    2 2 11
    3 3 11
    1 4 11
    2 5 11
    3 6 11

    So for the table above I would want it to return Visit ID 1 and 4 since they both below to Patient ID 1, whose visit 1 meets the criteria.

    Really sorry if I explained this way more complexly than necessary for a simple problem, thanks for all the help.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you should combine the left and right arm tables into one, with an additional column to indicate whether left or right, it would make any query much simpler

    however your query would be something like

    Code:
    SELECT *
    FROM (tblIdentifiers) LEFT JOIN tblRgtArm ON tblIdentifiers.PatientID=tblRgtArm.PatientID) LEFT JOIN tblLftArm ON tblIdentifiers.PatientID=tblLftArm.PatientID
    WHERE PatientID IN (SELECT PatientID FROM tblRgtArm AS T WHERE WstCirc<10)

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

Similar Threads

  1. Replies: 7
    Last Post: 12-12-2014, 11:58 AM
  2. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  3. Replies: 1
    Last Post: 07-09-2014, 10:42 AM
  4. Replies: 12
    Last Post: 05-30-2014, 07:08 AM
  5. Replies: 6
    Last Post: 01-12-2014, 03:11 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