Greg, this may DROP the constraints but I cant see how I can re-create them afterwards.

thanks greg

thanks davidt. works fine and looks more effiecient.

Thanks GRN and DavidBurrows, both solutions work very well.

I am a little surprised that

exec ('alter TABLE #temptable add TEMPCOL int identity (1,1) NOT NULL ')

works diferently to

alter TABLE...

I had a similar problem before. I slved it by doing the following in a stored procedure:

SELECT datepart(mm,date_requested) as my_month, datepart(year,date_requested) as the_year,count(date_requested) as my_count

into #temp_table

from accident_reg group by...

thanks Jonathan. very informative.

thanks dragon_1973

thanks yogi

Thanks for your answers. All sorted now.

thanks npeeters. Looks like we have to live with it.

strange that this is so as it seems so straightforward to do:

select

gym + park + hall as mysum,

3 * mysum...

ok.

Thanks for your help. Much appreciated.

quote:

---

Gary

thanks for your reply

I took your advice and, with a similar version of your code:

SELECT

o.organisation_shortname as org_shortname,

sum(q.media_cost_per_pos) as tot_media_cost,

sum(q.prod_cost_per_pos) as tot_prod_cost

FROM...

???

but you just dropped both WHERE clauses

???

I think I need the where criteria

-- Are saying to put the WHERE claus at the end ie:

SELECT

dbo.organisations.organisation_shortname as org_shortname,

sum(dbo.qry_positions_with_costs.media_cost_per_pos) as tot_media_cost,

sum(dbo.qry_positions_with_costs.prod_cost_per_pos) as...

i think that I sorted the problem [see script below] by using 2 select statements: a. SELECT INTO a temp table

b. doing a SUM on the temp table

if there is...

thank you both for excellent answers.

However where it fails though, is because I have to repeat the FROM clause. In the above example I really simplified my FROM clause....