AnsweredAssumed Answered

Help with query and omitting certain records

Question asked by alberto__1985 on May 21, 2015
Latest reply on May 22, 2015 by erolst

My name is Alberto and I'm kind of new to filemaker so excuse me if I miss important information on my question.  I will try to be as accurate as possible.


Ok here it goes I have two tables joined via a relationship.  Table A (Cases) and Table B (Applications).  The relationship is setup via field 1 (Case Number) in Table A (Cases) = field 2 (Case Number) in Table B (Applications).  I have a records that I want to do a search query on from Table B (Applications). First part of my query  (Query 1) is finding any records in which Field A (Application Type) = "EAD Valid Until" and  Field B (Application Date) = Get(CurrentDate) & "...01/01/2050".  This query is to find any upcoming renewals for application that are expiring in the future.  Sencond part of my query (Query 2) is finding any records in which Field A (Application Type) = "EAD Valid Until" and  Field B (Application Date) = "...01/01/2000" & Get(CurrentDate).  This query is to find any expired applications.  This application is renewed each year therefore any particular case can have one or more entries on the second query per case number, but only one for the first query.  Now the question is how can I do a (Query 2) and omit all expired applications that have a matching case number with a record in (Query 1).  The goal of this query is to find those cases which we forgot to renew an application.  If any one can help with a way to do this it would be greatly appreciated.

Outcomes