Results 1 to 10 of 10
  1. #1
    susanjb is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2014
    Posts
    5

    Question Query data based on another field

    Hi,

    I was wondering if anyone can help with this. I am a basic user of Access, so apologies if this is really easy.



    I have a list of id numbers, and I have a list of dates.
    I want to write a query criteria that will show me all the information on ID numbers that have a blank value in the end date column


    Can I write a criteria query that will show this?

    Source Data
    ID Start End
    1124 1/01/2014 1/02/2014
    1123 1/02/2014 1/02/2014
    1124 2/01/2014 15/02/2014
    1123 3/01/2014
    Want to display
    ID Start End
    1123 1/02/2014 1/02/2014
    1123 3/01/2014

    Is this possible?

    Thanks, Susan

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Use a sub-query to select where "end" is null and sort by "end".
    I think this should give you what you are looking for.

    SELECT [Source Data].ID, [Source Data].Start, [Source Data].End
    FROM [Source Data]
    WHERE ((([Source Data].ID) In (SELECT ID FROM [Source Data] WHERE End IS NULL)))
    ORDER BY [Source Data].ID, [Source Data].End DESC;

  3. #3
    susanjb is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    Thank you, I tried inputting the subquery and it returned this error.

    I wrote the subquery in the critera box for the ID. Is this correct?

    Click image for larger version. 

Name:	error.png 
Views:	21 
Size:	15.7 KB 
ID:	15350

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you include the IN operator? Is the subquery within ()? Does the subquery pull more than one field? Post the sql you attempted.
    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
    susanjb is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    (SELECT [Source Data].ID, [Source Data].Start, [Source Data].End FROM [Source Data]WHERE ((([Source Data].ID) In (SELECT ID FROM [Source Data] WHERE End IS NULL)))ORDER BY [Source Data].ID, [Source Data].End DESC)

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't know why you show the outermost () but don't see anything wrong with the sql.
    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.

  7. #7
    susanjb is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    Maybe I am putting it in the wrong spot?

    Click image for larger version. 

Name:	ERROR1.png 
Views:	21 
Size:	37.9 KB 
ID:	15351

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That looks ok to me.

    If you want to provide db, follow instructions at bottom of my post.
    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.

  9. #9
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    In Design-mode, under Column PATIENT_ID.
    The Criteria should show,

    IN ( SELECT PATIENT_ID FROM [ADMISSIONIS] WHERE ADMISSIONIS.SEP_DATE IS NULL )

  10. #10
    susanjb is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    Thank you!! that last one worked perfectly!!!!!!

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

Similar Threads

  1. Replies: 10
    Last Post: 07-19-2013, 02:05 PM
  2. Formatting a field based on other data
    By msmithtlh in forum Programming
    Replies: 2
    Last Post: 04-03-2013, 03:42 PM
  3. Replies: 2
    Last Post: 03-07-2013, 04:50 PM
  4. Replies: 7
    Last Post: 03-14-2012, 10:56 AM
  5. data field in query twice based on criteria
    By sandyg in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 07:47 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