Crosstab query calculations

[GraemeG]

Hello

I am not sure If I can explain this toowell, but here we go.

I have a table with components in and renew years this has a relationship with a schedule of rates (SOR) table.
I have created a crosstab query which shows the component name as row, and renew year as column heading. The value is a calculation of the rate in the SOR table against the component quantity. All works ok.
Now I have had to create another query from this crosstab in order to create more calculations. I.e This is a 30 yr profile and components have life cycles. So if the life cycle was 10 you would see the cost in for example. Year 1 Year 11 and Year 21 This all works ok.

However what I need now is some components in the same query have different cycles, So I cant simply say add costs in year 1 to 11 and 21.
I need to say IF KITCHEN (15) in year 1 then add costs to years 15 and then IF BOILER (10) in year 1 then add costs to years 11 and 21.

Can this be done?

Thanks

 

[Guus2005]

Yes. It can be done.

That's what i have to say because there's nothing you can't do with Access.

In reference to your problem i have to say that i don't understand your question.

You can do anything with queries if you add the proper fields, calculated fields e.t.c.
You can conditionally count certain values if you add a field that shows 1 of a field has such a value. As far as i understand your question that is a way to solve your problem... i think.

HTH

 

[GraemeG]

Right ta for the reply.

I have the following in a column heading of the query at the minute.

Year 11-15: CCur(Nz([Years 11-15],0))+([YR 1-5 Total]="Solid Fuel Fires (State Number)")

So I want it to contain any costs already in Years 11-15 but also add the costs for the record Solid Fuel Fires from the (Years 1-5 Total)

Can this be done?

 

[Guus2005]

I am not sure what the names of the columns are that you use. The code you provide doesn't make any sense to me.

Perhaps the following is what you are looking 4:

Year 11-15: CCur(Nz([Years 11-15],0)+nz([YR 1-5 Total],0))

If not, show me the entire query.

HTH

 

[GraemeG]

I am not sure what the names of the columns are that you use. The code you provide doesn't make any sense to me.

Perhaps the following is what you are looking 4:

Year 11-15: CCur(Nz([Years 11-15],0)+nz([YR 1-5 Total],0))

If not, show me the entire query.

HTH

Thanks. I have actually resolved this using an IF and then the Calculation.