I wondered if someone could help.

I am running a query from a table which holds data in format:

Year Period Value
---- ------ -----
2006 0 15000
2006 1 2000
2006 2 300
2006 3 150
2006 4 250
2006 5 12000

I am trying to seperate Period 2006 0 from the remaining Periods so that I am running a cumulative total for Period 1-5 and another for period 0

Can anyone help?

Thanks

Paul

Total for Period 0:
SELECT SUM([Value])
FROM tbl
WHERE Period = 0;

Total for all others:
SELECT SUM([Value])
FROM tbl
WHERE Period <> 0;

I have tried to input the above criteria into my select query, however the run does run but no results are shown.

Can anyone help?

Value or Values is the reserve word in Jet. This might be one of the reasom. Try using other field name something like TxtValue.

Ashfaque

Is there an easier way of showing results? Still having a problem running this query:

i.e in one column of my query

Period0: iif[period]=0,[value]

and

PeriodOthers: iif[period]=1-4, [value]

Have tried this format but reason an error: too complex to run report

I have run two columns:

Period20060: IIf([period]<>0,[valuehome])
Period2006: IIf([period]=0,[valuehome])

and receive restults for period=0 but not for <>0.

Can anyone help?

Don't you think false part in your IIF statement is messing?

I have an example for you that I used in my query which gives me desired result. Here it is.

IIf([SumOfPAmount]-([RunningAmount]-[Amount])>0,[SumOfPAmount]-([RunningAmount]-[Amount]),0)) AS Adjusted,

This would give you an idea I hope.

Ashfaque

I just wrote up that table in a temp database and the queries I gave you work fine using both the default field names (Field1, Field2 ...) and Year/Period/Value. Double check that you wrote them correctly using the SQL view of the query design, and that the name of the table is correct (it's probably not 'tbl').