UNION Query - three tables to one - error

[NigelBrown]

Hi All,
I have written the below UNION QUERY (I know its wrong but trying to show what I want to achieve) I would like all three tables to convert to one - this is only a one off every day and the result is about 400,000 rows combined. It works with two tables fine, I am not sure how to go about including the third SELECT, when I run the below code i get the error "Too many fields defined". Any help/advice would be greatly appreciated.

[Code=vb]
SELECT NY_Prophet.book , NY_Prophet.curr ency, NY_Prophet.coun try, NY_Prophet.isin , NY_Prophet.call put, NY_Prophet.inst rumentname, NY_Prophet.posi tion, NY_Prophet.mark etvalue, NY_Prophet.inst rumenttype, NY_Prophet.exch ange, NY_Prophet.expi rydate, NY_Prophet.stri keprice, NY_Prophet.rule ofexercise, NY_Prophet.cont ractsize, NY_Prophet.pric e, NY_Prophet.data basecode, NY_Prophet.idin st, NY_Prophet.idin stunderlying, NY_Prophet.reut erscode, NY_Prophet.prod ucttype, NY_Prophet.unde rlyingsperderiv ative, NY_Prophet.issu er, NY_Prophet.coun terpartycode, NY_Prophet.real isedpl_ytd, NY_Prophet.theo pl_ytd, NY_Prophet.divi dendpl_ytd, NY_Prophet.accr uedpl_ytd, NY_Prophet.misc pl_ytd, NY_Prophet.char gespl_ytd, NY_Prophet.coup onrate, NY_Prophet.prod uctflag, NY_Prophet.sedo l, NY_Prophet.reut ersfeedmultipli er, NY_Prophet.nomi nal, NY_Prophet.fund ing_ytd, NY_Prophet.real isedpl_life, NY_Prophet.theo pl_life, NY_Prophet.divi dendpl_life, NY_Prophet.accr uedpl_life, NY_Prophet.misc pl_life, NY_Prophet.char gespl_life, NY_Prophet.fund ing_life, NY_Prophet.cash typename, NY_Prophet.cash typeid, NY_Prophet.barg ainid, NY_Prophet.sett ledcash_life, NY_Prophet.unre alisedpl_life, NY_Prophet.Fiel d48, NY_Prophet.eurf xrate, NY_Prophet.theo pl_lifeeur, NY_Prophet.theo pl_ytdeur, NY_Prophet.tota ltheopl_ytd, NY_Prophet.tota ltheopl_life, NY_Prophet.tota ltheopl_ytdeur, NY_Prophet.tota ltheopl_lifeeur , NY_Prophet.real unrealisedpl_yt d, NY_Prophet.year endeurfxrate, NY_Prophet.uniq ueidentifier, NY_Prophet.flse ttledcasht0, NY_Prophet.flse ttledcasht1, NY_Prophet.flse ttledcasht2, NY_Prophet.flse ttledcasht3, NY_Prophet.flse ttledcasht4, NY_Prophet.flse ttledcasht5, NY_Prophet.flli stedbrokeragepl _ytd, NY_Prophet.espv alue, NY_Prophet.noti onalfixedvariab le, NY_Prophet.rece iveleg, NY_Prophet.payl eg, NY_Prophet.earl iesttradedate, NY_Prophet.real isedaccruedpl_l ife, NY_Prophet.unre alisedaccruedpl _life, NY_Prophet.real isedaccruedpl_y td, NY_Prophet.unre alisedaccruedpl _ytd, NY_Prophet.last coupondate, NY_Prophet.next coupondate, NY_Prophet.inte restmethod, NY_Prophet.clea ndirty, NY_Prophet.thir dpartychargespl _life, NY_Prophet.thir dpartychargespl _ytd, NY_Prophet.curr entperiodpurcha sedsold, NY_Prophet.prev iousperiodpurch asedsold, NY_Prophet.[couponpayment(c lean)], NY_Prophet.[couponpayment(d irty)], NY_Prophet.week endtiming, NY_Prophet.sett periodaccruedla g, NY_Prophet.row_ type_indicator, NY_Prophet.mark etmultiplier, NY_Prophet.migr ationcouponadj, NY_Prophet.ronn iecurrency
FROM NY_Prophet;
UNION ALL SELECT LON_Prophet.boo k, LON_Prophet.cur rency, LON_Prophet.cou ntry, LON_Prophet.isi n, LON_Prophet.cal lput, LON_Prophet.ins trumentname, LON_Prophet.pos ition, LON_Prophet.mar ketvalue, LON_Prophet.ins trumenttype, LON_Prophet.exc hange, LON_Prophet.exp irydate, LON_Prophet.str ikeprice, LON_Prophet.rul eofexercise, LON_Prophet.con tractsize, LON_Prophet.pri ce, LON_Prophet.dat abasecode, LON_Prophet.idi nst, LON_Prophet.idi nstunderlying, LON_Prophet.reu terscode, LON_Prophet.pro ducttype, LON_Prophet.und erlyingsperderi vative, LON_Prophet.iss uer, LON_Prophet.cou nterpartycode, LON_Prophet.rea lisedpl_ytd, LON_Prophet.the opl_ytd, LON_Prophet.div idendpl_ytd, LON_Prophet.acc ruedpl_ytd, LON_Prophet.mis cpl_ytd, LON_Prophet.cha rgespl_ytd, LON_Prophet.cou ponrate, LON_Prophet.pro ductflag, LON_Prophet.sed ol, LON_Prophet.reu tersfeedmultipl ier, LON_Prophet.nom inal, LON_Prophet.fun ding_ytd, LON_Prophet.rea lisedpl_life, LON_Prophet.the opl_life, LON_Prophet.div idendpl_life, LON_Prophet.acc ruedpl_life, LON_Prophet.mis cpl_life, LON_Prophet.cha rgespl_life, LON_Prophet.fun ding_life, LON_Prophet.cas htypename, LON_Prophet.cas htypeid, LON_Prophet.bar gainid, LON_Prophet.set tledcash_life, LON_Prophet.unr ealisedpl_life, LON_Prophet.Fie ld48, LON_Prophet.eur fxrate, LON_Prophet.the opl_lifeeur, LON_Prophet.the opl_ytdeur, LON_Prophet.tot altheopl_ytd, LON_Prophet.tot altheopl_life, LON_Prophet.tot altheopl_ytdeur , LON_Prophet.tot altheopl_lifeeu r, LON_Prophet.rea lunrealisedpl_y td, LON_Prophet.yea rendeurfxrate, LON_Prophet.uni queidentifier, LON_Prophet.fls ettledcasht0, LON_Prophet.fls ettledcasht1, LON_Prophet.fls ettledcasht2, LON_Prophet.fls ettledcasht3, LON_Prophet.fls ettledcasht4, LON_Prophet.fls ettledcasht5, LON_Prophet.fll istedbrokeragep l_ytd, LON_Prophet.esp value, LON_Prophet.not ionalfixedvaria ble, LON_Prophet.rec eiveleg, LON_Prophet.pay leg, LON_Prophet.ear liesttradedate, LON_Prophet.rea lisedaccruedpl_ life, LON_Prophet.unr ealisedaccruedp l_life, LON_Prophet.rea lisedaccruedpl_ ytd, LON_Prophet.unr ealisedaccruedp l_ytd, LON_Prophet.las tcoupondate, LON_Prophet.nex tcoupondate, LON_Prophet.int erestmethod, LON_Prophet.cle andirty, LON_Prophet.thi rdpartychargesp l_life, LON_Prophet.thi rdpartychargesp l_ytd, LON_Prophet.cur rentperiodpurch asedsold, LON_Prophet.pre viousperiodpurc hasedsold, LON_Prophet.[couponpayment(c lean)], LON_Prophet.[couponpayment(d irty)], LON_Prophet.wee kendtiming, LON_Prophet.set tperiodaccruedl ag, LON_Prophet.row _type_indicator , LON_Prophet.mar ketmultiplier, LON_Prophet.mig rationcouponadj , LON_Prophet.ron niecurrency
FROM LON_Prophet;
UNION ALL SELECT HK_Prophet.book , HK_Prophet.curr ency, HK_Prophet.coun try, HK_Prophet.isin , HK_Prophet.call put, HK_Prophet.inst rumentname, HK_Prophet.posi tion, HK_Prophet.mark etvalue, HK_Prophet.inst rumenttype, HK_Prophet.exch ange, HK_Prophet.expi rydate, HK_Prophet.stri keprice, HK_Prophet.rule ofexercise, HK_Prophet.cont ractsize, HK_Prophet.pric e, HK_Prophet.data basecode, HK_Prophet.idin st, HK_Prophet.idin stunderlying, HK_Prophet.reut erscode, HK_Prophet.prod ucttype, HK_Prophet.unde rlyingsperderiv ative, HK_Prophet.issu er, HK_Prophet.coun terpartycode, HK_Prophet.real isedpl_ytd, HK_Prophet.theo pl_ytd, HK_Prophet.divi dendpl_ytd, HK_Prophet.accr uedpl_ytd, HK_Prophet.misc pl_ytd, HK_Prophet.char gespl_ytd, HK_Prophet.coup onrate, HK_Prophet.prod uctflag, HK_Prophet.sedo l, HK_Prophet.reut ersfeedmultipli er, HK_Prophet.nomi nal, HK_Prophet.fund ing_ytd, HK_Prophet.real isedpl_life, HK_Prophet.theo pl_life, HK_Prophet.divi dendpl_life, HK_Prophet.accr uedpl_life, HK_Prophet.misc pl_life, HK_Prophet.char gespl_life, HK_Prophet.fund ing_life, HK_Prophet.cash typename, HK_Prophet.cash typeid, HK_Prophet.barg ainid, HK_Prophet.sett ledcash_life, HK_Prophet.unre alisedpl_life, HK_Prophet.Fiel d48, HK_Prophet.eurf xrate, HK_Prophet.theo pl_lifeeur, HK_Prophet.theo pl_ytdeur, HK_Prophet.tota ltheopl_ytd, HK_Prophet.tota ltheopl_life, HK_Prophet.tota ltheopl_ytdeur, HK_Prophet.tota ltheopl_lifeeur , HK_Prophet.real unrealisedpl_yt d, HK_Prophet.year endeurfxrate, HK_Prophet.uniq ueidentifier, HK_Prophet.flse ttledcasht0, HK_Prophet.flse ttledcasht1, HK_Prophet.flse ttledcasht2, HK_Prophet.flse ttledcasht3, HK_Prophet.flse ttledcasht4, HK_Prophet.flse ttledcasht5, HK_Prophet.flli stedbrokeragepl _ytd, HK_Prophet.espv alue, HK_Prophet.noti onalfixedvariab le, HK_Prophet.rece iveleg, HK_Prophet.payl eg, HK_Prophet.earl iesttradedate, HK_Prophet.real isedaccruedpl_l ife, HK_Prophet.unre alisedaccruedpl _life, HK_Prophet.real isedaccruedpl_y td, HK_Prophet.unre alisedaccruedpl _ytd, HK_Prophet.last coupondate, HK_Prophet.next coupondate, HK_Prophet.inte restmethod, HK_Prophet.clea ndirty, HK_Prophet.thir dpartychargespl _life, HK_Prophet.thir dpartychargespl _ytd, HK_Prophet.curr entperiodpurcha sedsold, HK_Prophet.prev iousperiodpurch asedsold, HK_Prophet.[couponpayment(c lean)], HK_Prophet.[couponpayment(d irty)], HK_Prophet.week endtiming, HK_Prophet.sett periodaccruedla g, HK_Prophet.row_ type_indicator, HK_Prophet.mark etmultiplier, HK_Prophet.migr ationcouponadj, HK_Prophet.ronn iecurrency
FROM HK_Prophet;

[/Code]

Thanks
Nigel

[patjones]

Nigel -

You could try something like this:

SELECT NY_Prophet.* FROM NY_Prophet
UNION ALL
(SELECT LON_Prophet.* FROM LON_Prophet
 UNION ALL 
 SELECT  HK_Prophet.* FROM HK_Prophet);

The field names in the result set will take those of the first SELECT statement.

Pat

[Stewart Ross]

Hi Nigel

You have approx 90 fields in each select statement. You are trying to union three such statements, and Access is telling you that you have too many fields to union - it can handle a maximum of 255 fields at any one time.

It seems to me that you have separate tables, but surely the New York, London and Hong Kong elements should just be defined as an attribute of a table?

Database table structures are typically narrow (not that many fields) but deep (many records). With 90 fields+ in each table and separate tables for NY, LON and HK when a single attribute could be used instead your design is clearly not normalised as yet, and until you do so you will be stuck in trying to sort this out. The 400,000 rows you mention is also a clue, because it points to unnormalised data where rows contain repeated groups which should be brought out into their own separate related tables.

Even if it means starting afresh, it is not too late to do so.

We have a useful article on database normalisation and table structures which is a good starting point.

-Stewart

[NeoPa]

I'd suggest :

SELECT *
FROM   NY_Prophet
UNION ALL
SELECT *
FROM   LON_Prophet
UNION ALL 
SELECT *
FROM   HK_Prophet

Except that it might take your attention away from Stewart's post, which is the most important thing you need to hear. If you overlook this now, you are introducing yourself to a world of complication and pain. The deeper you get into database work without incorporating a normalised structure, the worse it will be for you.

PS. Your originally posted code will also struggle with the extra semi-colons (;) you have in the middle of it. They are supposed to indicate the end of a SQL command set.