CODEWITHSUNDEEP
ms-access-2010
Angus
Angus

Reputation: 355

Need help setting up an Access query

An example of what I'm trying to do is given below. For each person, I want a query that will look at each reason and calculate a sum of points based on: if A-F are present the points will be calculated as follows for John 10+20+30+40+50-60, i.e., A+B+C+D+E-F. If F isn't present, then it's a straight sum of the points (for Paul).

ID  name    points  reason
1   John    10  A
2   John    20  B
3   John    30  C
4   John    40  D
5   John    50  E
6   John    60  F
7   Paul    5   A
8   Paul    10  B
9   Paul    15  C
10  Paul    20  D
11  Paul    25  E

Upvotes: 0

Views: 30

Answers (2)

n8wrl
n8wrl

Reputation: 19765

Try this:

Select [name], 
    Sum(IIF([reason] = "F", -[points], [points])) As TotalPoints
From YourTable
Group By [name]

Learn more about iif here: https://support.office.com/en-us/article/iif-function-32436ecf-c629-48a3-9900-647539c764e3

Upvotes: 2

Gustav
Gustav

Reputation: 56026

There is no difference between 10+20+30+40+50-60 and what you call a straight sum.

So, all you need is to group by the name:

Select [name], Sum([points]) As TotalPoints
From YourTable
Group By [name]

Upvotes: 0

Related Questions