Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Mar 10, 2020

IF Statement as filtering criteria

Need help with building an IF statement into access, very new user here.

 

In the screenshot below, the first table ("31109...") has two data points - WQ ID and WQ Rank.  the other tables provide detail on records that present in the WQ ID's.    The short version is for forecasting purposes (labor, etc) I need to report Monday mornings on volumes present in each of the WQ ID's.  Problem is how the data is stored - records show in each WQ ID with a WQ Entry Date, but should they leave the WQ there is no Leave Date available to help filter down multiple WQ returns.   So the original report returned multiple rows per record/account I could not trim down.  To work  around this, I created the WQ RANK data point as records all begin their lifecycle in WQ where RANK=2, and eventually progress to WQ where RANK=1, if not resolved timely.

 

My goal is to return detail (WQ ID and other data points) on all records that meet one of the two criteria below:

- in WQ with RANK=2, so long as there is no record of that account in WQ with RANK=1.

AND

-in WQ with RANK=1, even if there is a record in WQ with RANK=2.  Here we would exclude the record in where WQ RANK=2. 

 

Is this something that can be accomplished in a single criteria set in ACCESS or do I need to look at VBA, etc

Thanks in advance

Joe

 

access

4 Replies

  • George Hepworth's avatar
    George Hepworth
    Steel Contributor
    Mar 10, 2020
    Unfortunately, your screenshot got lost along the way somewhere.

    Actually, far better than screenshots would be the actual SQL from the query itself anyway. That and an explanation of the meanings of acronyms use. Sometimes we forget that the day-to-day content of our own work isn't familiar to others.

    Thanks.
    • JoeCavasin's avatar
      JoeCavasin
      Brass Contributor
      Mar 10, 2020

      SQL below:

       

      SELECT EPIC_SHD_FOLLOW_UP_HISTORY.WORKQUEUE_ID, Last(EPIC_SHD_FOLLOW_UP.WORKQUEUE_ENTRY_DATE) AS LastOfWORKQUEUE_ENTRY_DATE, EPIC_SHD_FOLLOW_UP.INVOICE_NUM, Sum(EPIC_SHD_FOLLOW_UP.OUTSTANDING_AMT) AS SumOfOUTSTANDING_AMT
      FROM [31109 Epic Work Queues] INNER JOIN (EPIC_SHD_FOLLOW_UP_HISTORY INNER JOIN EPIC_SHD_FOLLOW_UP ON EPIC_SHD_FOLLOW_UP_HISTORY.FOLLOW_UP_ID = EPIC_SHD_FOLLOW_UP.FOLLOW_UP_ID) ON [31109 Epic Work Queues].[WQ ID] = EPIC_SHD_FOLLOW_UP_HISTORY.WORKQUEUE_ID
      GROUP BY EPIC_SHD_FOLLOW_UP_HISTORY.WORKQUEUE_ID, EPIC_SHD_FOLLOW_UP.INVOICE_NUM
      HAVING (((Sum(EPIC_SHD_FOLLOW_UP.OUTSTANDING_AMT))>0));

      George Hepworth