If Condition Query in Access

[Vishan]

Hi All,

I have some data as below and i want query as below.

Mobile	activationdate	1-Sep	2-Sep	3-Sep	4-Sep	5-Sep	6-Sep	7-Sep	8-Sep	9-Sep
8826654878	9/1/2012	0	0	29.44	2.23	9.83	0	0	10.48	13.22
9910134774	9/1/2012	0	0	0	9.72	6.18	8	11.46	1.4	3.7
8826653406	9/1/2012	0	0	0	64.64	20	11.5	0	0	17
8800988135	9/2/2012	0	0	0	0	0	0	0	0	5
8527517271	9/2/2012	0	0	0	0	0	2.82	5.01	2.08	5.04
9871746193	9/2/2012	0	0	0	0	0	3.05	20.97	69.86	0.39
8800988030	9/3/2012	0	0	0	0	0	0	8.56	0	10.5

if activation date = 9/1/12 then sum (4days) 1st to 4th. mean activation day + 4 Days.

if there is any solution pls help.


thnx

[June7]

The posted file is Excel spreadsheet. Was that an export from Access db?

Do you intend to have a column for every day of the year? That is not a normalized data structure.

What do you mean by 'mean activation day'?

[Vishan]

i mean, i have huge data with it's activation date in date format for the any 1 month, and i also have day on day figure of each activation date (about respectively 45 Days)

i want query for (Activation Date + 4 Days),
exp.
If activation date=9/1/12 then sum from day1 to day4
If activation date=9/2/12 then sum from day2 to day5
If activation date=9/3/12 then sum from day3 to day6
.
.
.
.
If activation date=9/30/12 then sum from day30 to day33

[June7]

As I said, data structure not normalized. I don't understand your reply, it doesn't actually address the question I posted.

One way to do want you want with this data structure might be to create expressions in query that sum the fields in the groups of 4 you describe:

Calc1: [Sep 1] + [Sep 2] + [Sep 3] + [Sep 4]
Calc2: [Sep 2] + [Sep 3] + [Sep 4] + [Sep 5]
Calc3: [Sep 3] + [Sep 4] + [Sep 5] + [Sep 6]
etc

Then make the query a GROUP BY (Totals) query that groups on the date field.

Can include date criteria in each expression if you want:
Calc1: IIf(ActivationDate=#9/1/2012#, [Sep 1] + [Sep 2] + [Sep 3] + [Sep 4], Null)
Calc2: IIf(ActivationDate=#9/2/2012#, [Sep 2] + [Sep 3] + [Sep 4] + [Sep 5], Null)