Simple but difficult Access query ("SumIf-like")

General Questions
#1

I’m having an awful time creating what seems like should be a fairly simple access query. There are three fields, let’s call them Name, Age, and Earnings. My data looks like this:



Name	Age	Earnings
Joe	50	10
Joe	51	7
Joe	53	13
Joe	54	12
Bob	30	10
Bob	33	12
Bob	34	8
George (...)

What I need is the “cumulative” earnings–i.e., for each Name-Age combination, the sum of earnings for that person up to that age. It would look like this, with one output row per input row:



Name	Age	Earnings CumEarnings
Joe	50	10	10		
Joe	51	7	17
Joe	53	13	30
Joe	54	12	42
Bob	30	10	10
Bob	33	12	22
Bob	34	8	30
George (...)

Completely stuck after much experimentation. Any ideas, Access/SQL gurus? (I realize this would be easy to do in Excel, however, the purpose here is to replicate an Excel process in Access, because the size of the database is getting too big for Excel to handle).

#2

SELECT Table1.Name, Table1.Age, Sum(Table1_1.Earnings) AS SumOfEarnings, Table1_1.Name
FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.Name = Table1_1.Name
WHERE (((Table1_1.Age)<=[table1].[age]))
GROUP BY Table1.Name, Table1.Age, Table1_1.Name
ORDER BY Table1.Name, Table1.Age;

#3

Oh, just as an aside (and I realise you’re probably giving us example data), ‘Name’ might not be a great name for a field - because it’s a reserved word in some other contexts.

#4

Wow, thanks a lot. A few of this in the office were stuck on this one. I’m going to go through the code and figure out how it works, but do you have any “strategy” tips for similar jobs in the future?

And you’re right–“Name” is actually a different, and unique, identifier.

#5

(That would be “a few of us”)

#6

If you paste the SQL into an Access Query, it will build it for you in the designer view (you have to create the example table first, of course) - that will show you how this particular query works

But in general, what I did here was to realise that what you want to see is two different views of the same data - one simple extract and one conditional summary - sometimes this means you have to create one query to build your summary, then use that query’s results as a table in a second query, with other tables, or with another instance of the same table.

In plain English, this query is:

“I want to see name, age and earnings, then I want to see another view of earnings - a summary of earnings for that name, where age is the same or less”