Results 1 to 7 of 7
  1. #1
    Dgalaxy is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2020
    Posts
    5

    Query help to write a condition

    Hello Experts

    I am trying to pick the correct location for all my "Encounter" types based on effective date. I want to write something like if loc is null and if "Encounter" type effective date is less than "Enrollment" type effective date or if effective date is in between "Enrollment" type effective date and ENDT return the Loc value for "Enrollment" type. Can you please help me or suggest any other way this can be done
    Thank you I appreciate any help

    MemberNumber EffectiveDate ENDT Loc Type
    20040300416000 2/5/2019 10/30/2019 RUHS - LAKE ELSINORE Enrollment
    20040300416000 2/15/2019 12/31/2099


    Encounter
    20040300416000 3/5/2019 12/31/2099
    Encounter
    20040300416000 4/2/2019 12/31/2099
    Encounter
    20040300416000 4/23/2019 12/31/2099
    Encounter
    20040300416000 5/23/2019 12/31/2099
    Encounter
    20040300416000 8/9/2019 12/31/2099
    Encounter
    20040300416000 10/31/2019 2/3/2020 RUHS - CORONA Enrollment
    20040300416000 2/5/2020 12/31/2099
    Encounter
    20040300416000 3/9/2020 12/31/2099
    Encounter
    20040300416000 3/11/2020 12/31/2099 RUHS - CORONA Enrollment
    20040300416000 3/19/2020 12/31/2099
    Encounter
    20040300493400 8/2/2019 12/31/2099
    Encounter
    20040300493400 3/3/2020 12/31/2099
    Encounter
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    Criteria based on value in another record of same table gets complicated. Review this for an example of what you face http://allenbrowne.com/subquery-01.html#AnotherRecord
    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
    Dgalaxy is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2020
    Posts
    5
    Thank you for your response but I am a novice access user I went through the examples you provided and understood that I need to use sub queries but can you please give little more advice how I can use sub queries for my case here
    Thank you

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    A sample of desired output based on given data would be helpful. Can build a mockup in Excel.

    None of the Encounter records in database have value in Termination. There is no field ENDT.
    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
    Dgalaxy is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2020
    Posts
    5
    Max(IIf([Copy Of Encounter Data].TerminationDate Is Null,#12/31/2099#,[Copy Of Encounter Data].TerminationDate)) AS ENDT

    In my query i have this condition. I am attaching the desired output

    Thanks
    Attached Files Attached Files

  6. #6
    Dgalaxy is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2020
    Posts
    5
    Quote Originally Posted by June7 View Post
    A sample of desired output based on given data would be helpful. Can build a mockup in Excel.

    None of the Encounter records in database have value in Termination. There is no field ENDT.
    ************************************************** ****************
    MemberNumber EffectiveDate TerminationDate ClinicLocationID Type Enroll Number
    20040300416000 10/31/2019 2/3/2020 RUHS - CORONA Enrollment 2
    20040300416000 3/11/2020 12/31/2099 RUHS - CORONA Enrollment 3
    20040300416000 2/5/2019 10/30/2019 RUHS - LAKE ELSINORE Enrollment 1

    I want to add a new column Enroll Number and number them as 1,2, 3 etc based on the effective date can you please suggest how can i write it.
    Thanks
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    Worksheet data does not coincide with database data. What should happen if a member number does not have an enrollment record?

    I am not able to accomplish this with nested query. Trying to use domain aggregate functions but that is also getting too complicated. Think I would build a VBA function to accomplish.

    You want to number enrollment numbers for each member? Saving row number into table would require VBA. Otherwise, build a report and use RunningSum property of textbox along with Sorting & Grouping features.
    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.

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

Similar Threads

  1. Replies: 8
    Last Post: 10-28-2017, 03:09 PM
  2. How to write a query
    By concrete in forum Queries
    Replies: 3
    Last Post: 09-07-2016, 10:54 AM
  3. Help to write query?
    By mlandis in forum Queries
    Replies: 4
    Last Post: 02-17-2012, 09:42 AM
  4. How Do I Write This Query?
    By TxTcher in forum Forms
    Replies: 8
    Last Post: 08-13-2010, 01:50 PM
  5. How would I write this query?
    By tigers in forum Queries
    Replies: 1
    Last Post: 02-08-2010, 08:29 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 - Senior Forums