SQL | Cumulative Cost with multiple grouping and criteria

I have tried many variations of this SQL statement after much research on this site as well as others, but I'm missing something

My data table looks like:

.....Date.....Boolean...Group....Imp......Cost
....1/1/11.....TRUE.........A........High.......$100
....1/1/11.....FALSE........A........Med........$150
....1/1/11.....TRUE.........B........High.......$200
....2/1/11.....TRUE.........B........Low........$300

For each month, I want the cumulative cost grouped by "Group" and "Imp." Here is my SQL query right now, but the cumulative sums (on my much larger data set) are WAAAYYY off.

SELECT t1.Date,
       t1.Group,
       t1.Imp,
       Sum(t2.Cost) AS RunTotal
FROM datatable t1
INNER JOIN datatable t2 ON t1.Date >= t2.Date
W开发者_Python百科HERE t1.Boolean=TRUE
GROUP BY t1.Date,
         t1.Group,
         t1.Imp

Note that I can't use cross join since I'm using MS Access. Any ideas where the error is?

Have you tried not using the Join at all? This is the query I used, and it seems to work correctly.

SELECT [Date], [Group], Img, SUM(Cost) AS RunTotal
FROM datatable WHERE [Boolean] = True
GROUP BY [Date], [Group], Img

If you want your running total to be per group and per imp, you need to include that in your join as well:

Select t1.Date, t1.Group, t1.Imp, Sum(t2.Cost) as RunTotal
From datatable t1 
Inner Join datatable t2 on t1.Group = t2.Group and t1.Imp = t2.Imp and t1.Date >= t2.Date
WHERE t1.Boolean=TRUE
GROUP BY t1.Date, t1.Group, t1.Imp

Right now, you are joining with all records where t1.Date >= t2.Date, so that includes records for non-matching group/imp. Just run the following:

Select t1.Date, t1.Group, t1.Imp, t2.Date, t2.Group, t2.Imp, t2.Cost
From datatable t1 
Inner Join datatable t2 on t1.Date >= t2.Date
WHERE t1.Boolean=TRUE

You should see t2.Cost records you do not want in your sum.