Query To show Show Different Results From same group

[paulS30berks]

I wondered if someone could help.

I have 3 columns of data within a table and from a select query am trying to seperate certain groups.

Table 1

yr period valuehome

2006 0 100
2006 0 10
2006 1 1000
2006 1 800
2006 2 60
2006 2 50
2006 3 40
2006 3 10
2006 3 5
2006 3 70
2006 4 50
2006 4 5

I wish to show both results for period 0 and an accumulative total for periods 1-4

so results would be

year period Total sum

2006 0 110
2006 1-4 2090

Can anyone help?

thanks

Paul

 

[namliam]

Select Year, Iif([period]=0,"0","1-4") as NewPeriod, sum([valuehome])
from yourtable
group by Year,Iif([period]=0,"0","1-4")

Or something like that, I hope you get the idea....

 

[paulS30berks]

I have written the following expressions within my query:

period 1-4: Sum(IIf([Forms]![fixed asset register]![period]>"0",[valuehome]))

and

period 0: Sum(IIf([period]="0",[valuehome]))

but the query is too complex to run.

 

[paulS30berks]

Sorry maybe silly question, how can I write this within a select query:

Select Year, Iif([period]=0,"0","1-4") as NewPeriod, sum([valuehome])
from yourtable
group by Year,Iif([period]=0,"0","1-4")

 

[paulS30berks]

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?

 

[namliam]

I am not beeing payed for this, come on... You cannot expect someone to react the moment you post...

Try this:
Period20060: IIf([period]=0,[valuehome],0)
Period2006: IIf([period]=0,0,[valuehome])

On my sollution, take your normal query. Add the yr field and the valuehome.
Now add this field as a third field:
NewPeriod: Iif([period]=0,"0","1-4")

Now simply make it a group by/sum query....