sum within query for only select records

[smg]

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

[CJ_London]

sumif is an excel function not used in access. try

monthsLost: -sum([enddate] is null and monthsEarned<>0)

[smg]

I could not get that to work, the query prompts for FYPeriod so I would like 20213 to to show Bernie lost 2 from previous periods. If you wouldn't mind trying your solution in the attachment, I would appreciate.

[CJ_London]

try

monthslost: nz((SELECT SUM([Months Earned]) FROM sheet1 T WHERE employee=sheet1.employee AND fyperiod<sheet1.fyperiod AND [End Date] is not null),0)

however be aware your data is different to the example you posted and all text. And really does not go far enough to provide a proper solution

For example what happens when you get to October? The FY code becomes 202110? If you keep them as text, then there will not be any months less

?"20219"<"202110"
False

And what happens if Bernie 'loses' in March per your example, earns in April and loses again in May? should that be 1 or 3.

good luck with your project