Union query 1

[fordtran]

I have two tables with the same (value) fields and I need to add each field of the two tables together. My query does not work - can somebody help.

I am in Access 2000

S$ = "Select sum(total) as Ttotal,sum(seland) as Tseland,sum(sesea) as Tsesea,sum(meland) as Tmeland,sum(mesea) as Tmesea,sum(rotor) as Trotor,sum(glider) as Tglider,sum(motorglider) as Tmotorglider, sum(military) as Tmilitary,sum(ldgsday) as Tldgsday,sum(ldgsnight) as Tldgsnight,sum(night)as Tnight,sum(actualIF) as Tactualif,sum(simif) as Tsimif,sum(flightsim) as Tflightsim,sum(crossc) as Tcrossc, sum(pic) as Tpic,sum(sic) as Tsic,sum(dualrec) as Tdualrec,sum(instruct) as Tinstruct from hours union all previoushours"

Thanks

fordtran

 

[Remou]

I think you need a little more in the Union statement (

http://www.w3schools.com/sql/sql_union.asp).

At the very least:

[tt]<...> as Tinstruct from hours union all Select * From previoushours[/tt]

Which assumes that the fields in previoushours are in exactly the same order as hours.

You will need to [backet] hours as it is a reserved word.

It is always very acceptable to get a little more information than 'does not work'.

 

[r937]

is this what you're looking for?

select sum(total) as Ttotal
     , sum(seland) as Tseland
     , sum(sesea) as Tsesea
     , sum(meland) as Tmeland
     , sum(mesea) as Tmesea
     , sum(rotor) as Trotor
     , sum(glider) as Tglider
     , sum(motorglider) as Tmotorglider
     , sum(military) as Tmilitary
     , sum(ldgsday) as Tldgsday
     , sum(ldgsnight) as Tldgsnight
     , sum(night)as Tnight
     , sum(actualIF) as Tactualif
     , sum(simif) as Tsimif
     , sum(flightsim) as Tflightsim
     , sum(crossc) as Tcrossc
     , sum(pic) as Tpic
     , sum(sic) as Tsic
     , sum(dualrec) as Tdualrec
     , sum(instruct) as Tinstruct 
  from ( 
       select * 
         from hours 
       union all 
       select *
         from previoushours
       ) as data

r937.com | rudy.ca

 

[fordtran]

r937

Thanks rudy - have a star

fordtran