Calculating IIf statement using two fields

[DBS1]

I have a query where I'm trying to create a field that calculates based off of another field. I'm trying to create an IIf statement that would give me the Cumulative field based on the Segment field, i.e., If Segment=B, Sum A and B, and if Segment=C, Sum A, B, and C, etc. Appreciate if someone could point me in the right direction. Thanks.

*Desired field*
Area Segment Total Cumulative
East A 5 5
East B 7 12


East C 2 14
East D 14 28
West A 16 16
West B 9 25
West C 10 35
West D 7 42

[CJ_London]

the problem with very simplistic data is it often does not reflect reality so having got a simplistic solution you then find it does not meet reality.

But to meet your data, something like this

SELECT A.Area, A.Segment, A.Total, sum(B.Total) as Cumulative
FROM myTable A INNER JOIN myTable B ON A.area=B.area and B.segment<=A.segment
GROUP BY A.Area, A.Segment, A.Total
ORDER BY A.Area, A.Segment

Note this can only be done in SQL, you cannot user the query GUI

[DBS1]

Ajax - thanks for your quick response. One thing I forgot to mention is the source of the data is from qrySiteVisits. Also, what I'm trying to achieve is for Segment A in Area East to return the sum of B, C, and D for East, and the same for West, and for Segment B to return the sum of C and D and for C to return the amount for D. (I also have North and South but didn't include those originally for simplicity) If you could give me a sample code for this, it would be greatly appreciated.

[DBS1]

Shortly after I posted the last message, I was able to resolve my query in a round about way. I used another query that calculated the fields I was looking for by using IIf statements, then used IIf statements to meet the criteria of the segment A, B, and C fields. Not something I'd suggest for anyone to use, but it accomplished my goal for the report I need. Didn't want to waste anyone's time since I've found a solution.