Update table with results of crosstab query

[stickers]

Hi All.

I've got a real 'blue-sky' question here. This is what I want to do. I have a crosstab query with various items of furniture as the row headings, Years (2004, 2005 etc) as the column headings, and amounts of money as the values. SO you can see how much to spend on chairs in 2008, for instance.

For every project that is entered into the database, you would typically run this query once. All well and good. However, what I would like to do is store the costs in years against each item so that as the project database builds up, you can see ACROSS ALL PROJECTS (sorry for caps, thats the important bit), what you are going to spend on a certain item in a certain year.

So I've created a make table query for the first run. But what I want now, is an update query that says:
"WHERE MyTable!ItemName = MyCrosstab!ItemName AND MyTable!FieldName(Year) = MyCrosstab!FieldName(Year) UPDATE MyTable!Value to (MyTable!Value + MyCrosstab!Value)"

Can anyone help me with some proper SQL to do this?

Thanks,

Sarah %-)

 

[GingerR]

a little confused, sorry:
what does a 'project' consist of?
what is your table structure?
what does the final table look like (please provide example data as best you can here)

thanks--g

 

[stickers]

Hi,

thanks for getting back to me. A Project is a building or group of buildings, with furniture in each room.

The table that I've made from the crosstabs looks like this:

ItemName 2002 2003 2004 2005 2006 2007 .....
Chairs £15 £20 £0 £0 £10 £15 .....
Tables £42 £60 £34 £0 £76 £12 .....

But this is only for one project, so here, in 2004 we are spending nothing on chairs, and £34 on tables. The values will change for different projects with different start dates, different numbers of chairs etc.

What I want to be able to do is if I have the following crosstabs:

Project 1

ItemName 2002 2003 2004 2005 2006 2007 .....
Chairs £15 £20 £0 £0 £10 £15 .....

Project 2

ItemName 2002 2003 2004 2005 2006 2007 .....
Chairs £0 £25 £40 £10 £0 £10 .....


I can combine them like this:

All projects

ItemName 2002 2003 2004 2005 2006 2007 .....
Chairs £15 £45 £40 £10 £10 £25 .....


Is that a bit clearer?

Thanks, Sarah