Sum values over range of dates

[bbronshteyn]

Need Help!!!!

Here is the example...

If I have a huge table of dates and values for those dates and need to
sum based on a given range, how do I sum it up?

date 1 date 2 date 3 date 4 date 5
Sate 1 12 7 8 1 1
State 2 10 4 6 2 8
State 3 5 4 2 3 7


start date end date Sum would be???
Sate 1 2 5 17
State 2 1 3 20
State 3 2 4 9

But what is the formula I can use for this??? Please help!

 

[Guest]

You first use a union query to normalize your table/spreadsheet.
SELECT State, 1 as TheDate, [Date 1] as TheValue
FROM tblOfDates
UNION ALL
SELECT State, 2, [Date 2]
FROM tblOfDates
UNION ALL
SELECT State, 3, [Date 3]
FROM tblOfDates
UNION ALL
SELECT State, 4, [Date 4]
FROM tblOfDates
UNION ALL
SELECT State, 5, [Date 5]
FROM tblOfDates;

You can then create a totals query like:
SELECT State, Sum(TheValue)
FROM quniNormalized
WHERE State = "State 1" AND TheDate Between 2 and 5;