# Simple but difficult Access query ("SumIf-like") **URL:** \ **Category:** Factual Questions\ **Created:** [August 10, 2007, 2:20pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131 "2007-08-10T14:20:59Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\ **Post date:** [August 10, 2007, 2:20pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/1 "2007-08-10T14:20:59Z")
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: ```auto 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: ```auto 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). ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [August 10, 2007, 2:34pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/2 "2007-08-10T14:34:00Z")
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; ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [August 10, 2007, 2:39pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/3 "2007-08-10T14:39:02Z")
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. ---
**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\ **Post date:** [August 10, 2007, 2:42pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/4 "2007-08-10T14:42:33Z")
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. ---
**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\ **Post date:** [August 10, 2007, 2:50pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/5 "2007-08-10T14:50:46Z")
(That would be “a few of _us_”) ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [August 10, 2007, 3:00pm UTC](https://boards.straightdope.com/t/simple-but-difficult-access-query-sumif-like/415131/6 "2007-08-10T15:00:13Z")
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”