Attached is a table in which I loaded a small result set for ease. I want to further query to be able to tell how many months Terminated employees lost that were previously earning.
I have concatenated the FY Year and FY Period and user created prompts.
Table looks like this:
FY Year FY Period FYPeriod Employee First Name End Date Months Earned Months Lost 2021 1 20211 234 Bernie 12/21/2020 1 0 2021 2 20212 234 Bernie 12/21/2020 1 0 2021 3 20213 234 Bernie 12/21/2020 0 0 2021 3 20213 173 Clifton 1 0 2021 4 20214 173 Clifton 1 0
For the 20213 Period I would like query to return a sum of the Months Lost for all the previous months where there is a Termination date populated and the FYPeriod is less than that prompted, so the result would look like this. I have tried and failed trying SUMIF, wondering if this would require a DSUM? I have no idea how to make this work.
FY Year FY Period FYPeriod Employee First Name End Date Months Earned Months Lost 2021 3 20213 234 Bernie 12/21/2020 0 2 2021 3 20213 173 Clifton 1 0