This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
My query give me an error TO MANY FIELDS DEFINED. When i count the fields i have only used 226.
What is causing this?
Thanks for any help
ed
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Not without using subreports because once tied together they will be over 255.
Side note: Sounds like the person who advised you is using far less of the data in the tables than you, is that so?
Not into word into the window on the web page in the link I posted.
Hmm, yep you need to bring it into a clean one again. Note, what you downloaded was probably flat file format which means that is the way they display it not store it.
Passing on secret tip....
To format queries for easier reading, go to...
Just copy in the SQL and click the Format SQL button. (Really wish that was built into Access!)
No problem glad to help. And hopefully, this post never disappears!
No problem... will get a message even if you reply 10 years from now! :)
Hmm, could be one of those deceptive messages because there is also a 1.024 character limit and spaces count!
That said, a query with that many fields is the *real* concern. It sounds like an UN-normalized structure. Why so many fields in the query?
SELECT PL5LHX4.tTRK, PL5LHX4.tDATE, PL5LHX4.tPGM, PL5LHX4.tHOR, IIf([NB01]=1,"Juc-","") AS A1, IIf([NB02]=1,"BO-","") AS B1, IIf([NB03]=1,"BF-","") AS C1, IIf([NB06]=1,"F&R-","") AS F1, IIf([NB07]=1,"PjT-","") AS G1, IIf([NB08]=1,"BTL-","") AS H1, IIf([NB09]=1,"FC-","") AS I1, IIf([NB10]=1,"Ped-","") AS J2, IIf([NB11]=1,"Ped*-","") AS K1, IIf([NB12]=1,"T30-","") AS L1, IIf([NB13]=1,"T*-","") AS M1, IIf([NB14]=1,"TJ-","") AS N1, IIf([NB15]=1,"TJ*-","") AS O1, IIf([NB16]=1,"J14-","") AS P1, IIf([NB17]=1,"J*-","") AS Q1, IIf([NB18]=1,"Pac-","") AS R2, IIf([NB19]=1,"LKi-","") AS T5, IIf([NB20]=1,"H4c-","") AS U1, IIf([NB21]=1,"Frh-","") AS V1, IIf([NB22]=1,"Clm*-","") AS W1, IIf([NB04]=1,"R1-","") AS R6, IIf([NB05]=1,"R2-","") AS R7, IIf([NN00]=1,"Ped-","") AS A, IIf([NN01]=1,"LOS-","") AS B, IIf([NN02]=1,"HTk-","") AS C, IIf([NN03]=1,"HSu-","") AS D, IIf([NN04]=1,"HCl-","") AS E, IIf([NN05]=1,"J%-","") AS F, IIf([NN06]=1,"T%-","") AS G, IIf([NN07]=1,"T30-","") AS H, IIf([NN08]=1,"TJ-","") AS I, IIf([NN09]=1,"TTk-","") AS J1, IIf([NN10]=1,"TCl-","") AS K, IIf([NN12]=1,"Tlay-","") AS M, IIf([NN13]=1,"T10-","") AS N, IIf([NN14]=1,"Str-","") AS O, IIf([NN15]=1,"J14-","") AS P, IIf([NN16]=1,"Lay-","") AS Q, IIf([NN17]=1,"TT-","") AS R1, IIf([NN18]=1,"Sor-","") AS S, IIf([NN19]=1,"Pac-","") AS T2, IIf([NN20]=1,"Lra-","") AS U, IIf([NN21]=1,"Sea-","") AS V, IIf([NN22]=1,"f3-","") AS W, IIf([NN88]=1,"BAD-","") AS W2, IIf([NN86]=1,"MLF-","") AS W3, PL5LHX4.nMLO, IIf([N365_STA]=0,0,[N365_WIN]+[N365_PLA]+[N365_SHO]) AS H36ITM, ALL_HX9.n365_STA, IIf([N365_STA]=0,0,[N365_WIN]) AS H36W, IIf([PL5LHX4.NCLCH] Between -6 And -9,1,IIf([PL5LHX4.NCLCH] Between -10 And -50,2,0)) AS CLASS, PL4LHX4.C10, PL4LHX4.C11, PL4LHX4.C12, PL4LHX4.C13, PL4LHX4.C14, PL4LHX4.C15, PL4LHX4.C16, PL4LHX4.C17, PL4LHX4.C18, PL4LHX4.CHG1, IIf([PL5LHX4.NTRCH]=1,"1C",IIf([PL5LHX4.NTRCH]=2,"1NC",IIf([PL5LHX4.NTRCH]=3,"2C",IIf([PL5LHX4.NTRCH]=4,"2NC",IIf([PL5LHX4.NTRCH]=5,"3C",IIf([PL5LHX4.NTRCH]=6,"3NC","")))))) AS TCC, PL5LHX4.DS2, PL5LHX4.DS3, PL5LHX4.SF2, PL5LHX4.SF3, ALL_HX5.nPP1DIS, ALL_HX5.nPP1SUR, PL5LHX4.SF1, PL5LHX4.DS1, ALL_HXV_1.tPP1TRK, IIf([NPP1DIS]<8 And [PL5LHX4.NDIST]>=8 And [PL5LHX4.NSURF]=2,1,0) AS STR, IIf([NDIST_STA]=0,0,[NDIST_WIN]) AS HDSW, ALL_HX9.nDIST_STA, IIf([NDIST_STA]=0,0,[NDIST_WIN]+[NDIST_PLA]+[NDIST_SHO]) AS HDSITM, PL5LHX4.nTOUR, Format(Val(Nz([PL5LHX4.TPGM],0)),"00000000") & Right(Nz([PL5LHX4.TPGM],0),1) AS PN, IIf(([NPP1CSF]>=[NPP2CSF]) And ([NPP2CSF]>=[NPP3CSF]) And [NLIFE_STA]>=4,2,IIf(([NPP1CSF]>=[NPP2CSF]) And ([NPP2CSF]>=[NPP4CSF]) And [NLIFE_STA]>=4,2,IIf(([NPP1CSF]>=[NPP3CSF]) And ([NPP3CSF]>=[NPP4CSF]) And [NLIFE_STA]>=4,2,IIf(([NPP2CSF]>=[NPP3CSF]) And ([NPP3CSF]>=[NPP4CSF]) And [NLIFE_STA]>=4,2,0)))) AS CSFP, ALL_HXN.nTBUZZ, ALL_HXN.nTNEG, PL5LHX4.nLAY, IIf([NN11]=1,"TDS-","") AS L, IIf([PL5LHX4.NCOURSE]=1,"MD",IIf([PL5LHX4.NCOURSE]=2,"MT",IIf([PL5LHX4.NCOURSE]=3,"ID",IIf([PL5LHX4.NCOURSE]=4,"IT",IIf([PL5LHX4.NCOURSE]=5,"AM",IIf([PL5LHX4.NCOURSE]=6,"STP","")))))) AS NCOURSE1, ALL_HXV_1.nDIST, ALL_HX9.nLIFE_STA, IIf([DS11]=1 And [SF1]=1,[ALL_HX5.NPP1PAC]-[PL5LHX4.NEPR_PAC],-99) AS PPA1, IIf([DS22]=1 And [SF2]=1,[ALL_HX5.NPP2PAC]-[PL5LHX4.NEPR_PAC],-99) AS PPA2, IIf([DS33]=1 And [SF3]=1,[ALL_HX5.NPP3PAC]-[PL5LHX4.NEPR_PAC],-99) AS PPA3, IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP2PAC]) And ([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP3PAC]) And [NLIFE_STA]>=3,3,IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP2PAC]) And ([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP4PAC]) And ([NLIFE_STA]>=4) And ([ALL_HX5.NPP4PAC]>0),3,IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And ([NLIFE_STA]>=4) And ([ALL_HX5.NPP4PAC]>0),3,IIf(([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And ([NLIFE_STA]>=4) And ([ALL_HX5.NPP4PAC]>0),3,0)))) AS PPAC, PL4LHX4.tLTRIP, IIf([F60]=2,1,IIf([F60]=2,1,0)) AS FF60, IIf(([NPURSE1]+5)<=[PL4LHX4.NPURS],1,0) AS PPP1, IIf([LIV]=2 And [LIV1]=2 And [LIV2]=2 And [NLIFE_STA]>=4,2,IIf([LIV]=2 And [LIV1]=2 And [LIV3]=2 And [NLIFE_STA]>=4,2,IIf([LIV]=2 And [LIV2]=2 And [LIV3]=2 And [NLIFE_STA]>=4,2,IIf([LIV1]=2 And [LIV2]=2 And [LIV3]=2 And [NLIFE_STA]>=4,2,0)))) AS LLIVV1, IIf([NRUNS_SOLO] Between 1 And 3,1,0) AS [E-LS], IIf([PL5LHX4.RCLASS]=1,2,IIf([PL5LHX4.RCLASS]=2,1,0)) AS RC, PL5LHX4.nLUCK, PL5LHX4.nKAT, PL5LHX4.nIMPROVE, PL5LHX4.nLONG, PL5LHX4.nOD_SCORE, IIf(([NPP1LP]>=[NPP2LP]) And ([NPP2LP]>=[NPP3LP]) And ([NLIFE_STA]>=3) And ([NPP4LP]>0),2,IIf(([NPP1LP]>=[NPP2LP]) And ([NPP2LP]>=[NPP4LP]) And ([NLIFE_STA]>=4) And ([NPP4LP]>0),2,IIf(([NPP1LP]>=[NPP3LP]) And ([NPP3LP]>=[NPP4LP]) And ([NLIFE_STA]>=4) And ([NPP4LP]>0),2,IIf(([NPP2LP]>=[NPP3LP]) And ([NPP3LP]>=[NPP4LP]) And ([NLIFE_STA]>=4) And ([NPP4LP]>0),2,0)))) AS LPP, IIf(([NPP1FR1]>=[NPP2FR1]) And ([NPP2FR1]>=[NPP3FR1]) And ([NLIFE_STA]>=3) And ([NPP4FR1]>0),3,IIf(([NPP1FR1]>=[NPP2FR1]) And ([NPP2FR1]>=[NPP4FR1]) And ([NLIFE_STA]>=4) And ([NPP4FR1]>0),2,IIf(([NPP1FR1]>=[NPP3FR1]) And ([NPP3FR1]>=[NPP4FR1]) And ([NLIFE_STA]>=4) And ([NPP4FR1]>0),2,IIf(([NPP2FR1]>=[NPP3FR1]) And ([NPP3FR1]>=[NPP4FR1]) And ([NLIFE_STA]>=4) And ([NPP4FR1]>0),2,0)))) AS LF1, IIf([PL5LHX4.DFR1]>=1,1,0) AS FR1D, IIf([LIV]=2 And [DS1]=1 And [SF1]=1,5,IIf([LIV]=2 And [DS1]=1,3,IIf([LIV]=2,1,0))) AS QL1, IIf([LIV1]=2 And [DS2]=1 And [SF2]=1,5,IIf([LIV1]=2 And [DS2]=1,3,IIf([LIV1]=2,1,0))) AS QL2, IIf([LIV2]=2 And [DS3]=1 And [SF3]=1,5,IIf([LIV2]=2 And [DS3]=1,3,IIf([LIV2]=2,1,0))) AS QL3, IIf([LIV3]=2 And [DS4]=1 And [SF4]=1,5,IIf([LIV3]=2 And [DS4]=1,3,IIf([LIV3]=2,1,0))) AS QL4, IIf([SOR1]>=3 And [NPP1FIN] Between 1 And 3,5,IIf([SOR1] Between -3 And 3 And [NPP1FIN] Between 1 And 3,3,IIf([SOR1]<-3 And [NPP1FIN] Between 1 And 3,1,0))) AS QSOR1, IIf([SOR2]>=3 And [NPP2FIN] Between 1 And 3,5,IIf([SOR2] Between -3 And 3 And [NPP2FIN] Between 1 And 3,3,IIf([SOR2]<-3 And [NPP2FIN] Between 1 And 3,1,0))) AS QSOR2, IIf([SOR3]>=3 And [NPP3FIN] Between 1 And 3,5,IIf([SOR3] Between -3 And 3 And [NPP3FIN] Between 1 And 3,3,IIf([SOR3]<-3 And [NPP3FIN] Between 1 And 3,1,0))) AS QSOR3, PL4LHX4.tLTRK, PL5LHX4.NLONEDD, PL5LHX4.NLONELUCK, PL5LHX4.NLONEKAT6, PL4LHX4.nCHG, ALL_HX44.nAML, PL5LHX4.rK2, IIf([PL5LHX4.RK]+[PL5LHX4.RHTR]<=6,1,0) AS KH, IIf(([PL1L.NLDIST]>=8 And [PL1L.NDIST]<8) And ([PL5LHX4.RFR1]=1 Or [PL5LHX4.RPED]=1 Or [PL5LHX4.TTPG] Like "*+" Or [PL5LHX4.NPED]>=550 Or [PL5LHX4.RFX]=1 Or [PL4LHX4.NRAZOR]>1) And ([PL1L.NCLCH]<=2),1,0) AS [R-S], IIf(([PL1L.NLDIST]<8 And [PL1L.NDIST]>=8) And ([PL5LHX4.RFR1]=1 Or [PL5LHX4.RPED]=1 Or [PL5LHX4.TTPG] Like "*+" Or [PL5LHX4.NPED]>=550 Or [PL5LHX4.RSP]=1 Or [PL4LHX4.NRAZOR]>0) And ([PL1L.NCLCH]<=2),1,0) AS [S-R], PL5LHX4.nFR1_DOM, PL5LHX4.nFR1_ADV, PL4LHX4.rK2, IIf([PL5LHX4.NKAT]>=6,1,0) AS KT6, [K22]+[WKSC1]+[KT6]+[PL4LHX4.NLONERAZOR] AS FTS, [RSYS1]+[RSYS2]+[RSYS3]+[RSYS4] AS SYST, IIf([PL5LHX4.NRS]=1,"F",IIf([PL5LHX4.NRS]=2,"E",IIf([PL5LHX4.NRS]=3,"P",IIf([PL5LHX4.NRS]=4,"S",IIf([PL5LHX4.NRS]=5,"R",""))))) AS RS, ALL_HX5.nPP1FIN, ALL_HX5.nPP2FIN, ALL_HX5.nPP3FIN, PL5LHX4.nQP, PL5LHX4.rFr1, PL5LHX4.rLP, PL5LHX4.rPAC, PL5LHX4.rPER, ALL_HXV_1.nPP1ODDS, IIf([ALL_HX5.NFNBL1]>=10,1,IIf([ALL_HX5.NFNBL2]>=10,2,0)) AS UT, ALL_HX9.nDIST_WIN, PL5LHX4.nPED, PL5LHX4.rPED, PL5LHX4.nFTI, ALL_HXN.nB11, PL4LHX4.dPED, ALL_HXN.nB10, IIf([PL5LHX4.RHTR]=1,2,IIf([PL5LHX4.RHTR] Between 2 And 3,1,0)) AS H5, IIf([NB11]=1 Or [NB12]=1,1,0) AS HPED, IIf([NTBUZZ]>=3 And [PL5LHX4.NMLO]>=5,1,0) AS [BUZ5/1], IIf([PL5LHX4.NIMPROVE] Between 3 And 4,1,IIf([PL5LHX4.NIMPROVE] Between 5 And 15,3,0)) AS IMP1, IIf([PL5LHX4.RK]=1,2,IIf([PL5LHX4.RK] Between 2 And 3,1,0)) AS K5, IIf([F60] Between 1 And 3,1,0) AS F61, IIf(1/2*([LIV]+[LIV1]+[LIV2]+[LIV3])>=3,1,0) AS LTT, IIf([PL5LHX4.NKAT] Between 6 And 7,1,IIf([PL5LHX4.NKAT] Between 8 And 15,3,0)) AS KAT6, ALL_HXV_1.nPP1SURF, PL4LHX4.nHAG, 1/2*([LIV]+[LIV1]+[LIV2]+[LIV3]) AS LT, IIf([PL5LHX4.NCLCH] Between -6 And -9,1,IIf([PL5LHX4.NCLCH] Between -10 And -50,2,0)) AS CLASS1, IIf(([PL5LHX4.NLONG]=2 Or [PL5LHX4.NLONG]=4) And [PL5LHX4.RFR1]=1,1,0) AS [$F], IIf([PL4LHX4.NRAZOR]=1,1,IIf([PL4LHX4.NRAZOR] Between 2 And 4,2,0)) AS RAZ, IIf(([PL5LHX4.TWK1] Like "A5G") And [PL5LHX4.TWK1] Is Not Null,1,IIf(([PL5LHX4.TWK2] Like "A5G") And [PL5LHX4.TWK2] Is Not Null,1,IIf(([PL5LHX4.TWK3] Like "A5G") And [PL5LHX4.TWK3] Is Not Null,1,IIf(([PL5LHX4.TWK4] Like "A5G") And [PL5LHX4.TWK4] Is Not Null,1,0)))) AS GAT, IIf([PL5LHX4.NTOUR]=1,2,IIf([PL5LHX4.NTOUR]=2,1,0)) AS TD, [PL5LHX4.NFR1_DOM]+[PL5LHX4.NFR1_ADV]+[BUZ5/1]+[TROI]+[F61]+[KAT6]+[P600]+[E-LS]+[FTI]+[IMP1]+[LT]+[HPED]+[K5]+[H5]+[FR1D]+[CLASS]+[KH]+[RAZ]+[PL5LHX4.NLONEDD]+[PL5LHX4.NLONELUCK]+[PL5LHX4.NLONEKAT6]+[$F]+[WWW]+[TD]+[GAT] AS [SUM], IIf(([PL5LHX4.TWK1] Like "[AB]*" And [PL5LHX4.TWK1] Like "*[567]*"),1,IIf(([PL5LHX4.TWK2] Like "[AB]*" And [PL5LHX4.TWK2] Like "*[567]*"),1,IIf(([PL5LHX4.TWK2] Like "[AB]*" And [PL5LHX4.TWK3] Like "*[567]*"),1,IIf(([PL5LHX4.TWK4] Like "[AB]*" And [PL5LHX4.TWK4] Like "*[567]*"),1,0)))) AS WWW, [NPLS]+[PPER]+[CLASS]+[LPP]+[SY4]+[LT]+[SY1] AS NPLS1, IIf([NSYS1]=1,2,IIf([NSYS1] Between 2 And 3,1,0)) AS SY1, ALL_HX44.rSYS1, ALL_HX44.rSYS2, ALL_HX44.rSYS4, ALL_HX44.rSYS3, [DEM]+[NTBUZZ]+[$$]+[KL45]+[$F]+[PL5LHX4.NTOUR]+[R-S]+[S-R]+[PL5LHX4.NKENTENDER]+[PL5LHX4.NPRICEPLAY]+[KAT6]+[PL5LHX4.NLUCK]+[IMP]+[PL5LHX4.NLONELUCK]+[PL5LHX4.NLONEKAT6]+[PL5LHX4.NLONEDD]+[HHH]+[KMS]+[CSFT1]+[PL5LHX4.NBLOWOUT]+[E-LS]+[RAZ]+[FR1D] AS PLS1, IIf(([PL4LHX4.NKLINE]<=1/2*[PL5LHX4.NMLO]) And ([PL5LHX4.NKLINE]<=1/2*[PL5LHX4.NMLO]),3,IIf([PL4LHX4.NKLINE]<=1/2*[PL5LHX4.NMLO] Or [PL5LHX4.NKLINE]<=1/2*[PL5LHX4.NMLO],2,0)) AS KL45, IIf([PL4LHX4.NOD_SCORE]>=75 And [PL5LHX4.NOD_SCORE]>=75,3,IIf([PL4LHX4.NOD_SCORE]>=75 Or [PL5LHX4.NOD_SCORE]>=75,2,0)) AS DEM, IIf(([PL4LHX4.NLONG]=2 Or [PL4LHX4.NLONG]=4) And ([PL5LHX4.NLONG]=2 Or [PL5LHX4.NLONG]=4),2,IIf(([PL4LHX4.NLONG]=2 Or [PL4LHX4.NLONG]=4) Or ([PL5LHX4.NLONG]=2 Or [PL5LHX4.NLONG]=4),1,0)) AS [$$], [PLS1]+[NPLS1]+[LLIVV]+[F61]+[RC]+[SYS2]+[SYS3]+[PPAC]+[LF1]+[TROI]+[PL5LHX4.NFR1_ADV]+[PL5LHX4.NFR1_DOM]+[NKK]+[SYS4] AS TOTAL, IIf([LIV]=2 And [LIV1]=2 And [LIV2]=2 And [NLIFE_STA]>=4,3,IIf([LIV]=2 And [LIV1]=2 And [LIV3]=2 And [NLIFE_STA]>=4,3,IIf([LIV]=2 And [LIV2]=2 And [LIV3]=2 And [NLIFE_STA]>=4,3,IIf([LIV1]=2 And [LIV2]=2 And [LIV3]=2 And [NLIFE_STA]>=4,3,0)))) AS LLIVV, IIf([RSYS2]=1,2,IIf([RSYS2]=2,1,0)) AS SYS2, IIf([RSYS1]=1,2,IIf([RSYS1]=2,1,0)) AS SYS1, IIf([RSYS3]=1,2,IIf([RSYS3]=2,1,0)) AS SYS3, PL5LHX4.rK, PL5LHX4.rHTR, PL5LHX4.nK, IIf([PL5LHX4.NFAV1]=1,"A",IIf([PL5LHX4.NFAV1]=2,"B",IIf([PL5LHX4.NFAV1]=3,"C",IIf([PL5LHX4.NFAV1]=4,"D",IIf([PL5LHX4.NFAV1]=5,"F",""))))) AS FAV12, IIf([PL5LHX4.NFAV2]=1,"A",IIf([PL5LHX4.NFAV2]=2,"B",IIf([PL5LHX4.NFAV2]=3,"C",IIf([PL5LHX4.NFAV2]=4,"D",IIf([PL5LHX4.NFAV2]=5,"F",""))))) AS FAV22, PL5LHX4.nCLch, DateDiff("d",[PL4LHX4.tLdate],[twk1_date]) AS dd1, DateDiff("d",[PL4LHX4.TLdate],[twk2_date]) AS dd2, DateDiff("d",[PL4LHX4.TLdate],[twk3_date]) AS dd3, DateDiff("d",[PL4LHX4.TLdate],[twk4_date]) AS dd4, PL5LHX4.tWK1, PL5LHX4.tWK2, PL5LHX4.tWK3, PL5LHX4.tWK4, ALL_HX9.tWK1_TRACK, ALL_HX9.tWK2_TRACK, ALL_HX9.tWK3_TRACK, ALL_HX9.tWK4_TRACK, PL5LHX4.nKLine, [NB06]+[NB08]+[NB20]+[PL5LHX4.NFR1_DOM]+[PL5LHX4.NFR1_ADV]+[KT6]+[TURR]+[TUR]+[ALL_HX44.NTSPOT]+[NKENTENDER] AS TURF, ALL_HX44.NTSPOT, IIf([BLSUM]>3 And [ALL_HX5.NPP1SUR]=2 And [PL4LHX4.NSURF]=1,1,0) AS TSP, PL5LHX4.nXFAV, IIf([LIV]=0 And [LIV1]=2 And [LIV2]=2 And [NLAY]>65,1,IIf([LIV]=0 And [LIV]=0 And [LIV1]=0 And [LIV2]=2 And [LIV3]=2 And [NLAY]>65,2,0)) AS LIVSP, PL4LHX4.tPLSELECTED, PL5LHX4.nKGAP, PL5LHX4.nUNK, ALL_HX44.NMSPOT, PL5LHX4.rSP, PL5LHX4.nCOURSE, PL5LHX4.F60, PL5LHX4.nKENTENDER, IIf([NSYS4]=1,2,IIf([NSYS4] Between 2 And 3,1,0)) AS SY4, IIf([PL5LHX4.NIMPROVE] Between 3 And 4,1,IIf([PL5LHX4.NIMPROVE] Between 5 And 15,3,0)) AS IMP, IIf([PL5LHX4.NK]>=112,2,IIf([PL5LHX4.NK] Between 110 And 111,1,0)) AS NKK, IIf([PL4LHX4.RHTR]<3 And [PL5LHX4.RHTR]<3,3,IIf([PL4LHX4.RHTR]<3 Or [PL5LHX4.RHTR]<3,2,IIf([PL4LHX4.RHTR]<6 Or [PL5LHX4.RHTR]<6,1,0))) AS HHH, IIf([RSYS4]=1,2,IIf([RSYS4] Between 2 And 3,1,0)) AS SYS4, PL5LHX4.nRACE, [PPER]+[CLASS]+[LPP]+[SY4]+[LT]+[SY1] AS ZZ, IIf(([ALL_HX5.NPP1PER]>=[ALL_HX5.NPP2PER]) And ([ALL_HX5.NPP2PER]>=[ALL_HX5.NPP3PER]) And [NLIFE_STA]>=3,3,IIf(([ALL_HX5.NPP1PER]>=[ALL_HX5.NPP2PER]) And ([ALL_HX5.NPP2PER]>=[ALL_HX5.NPP4PER]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP1PER]>=[ALL_HX5.NPP3PER]) And ([ALL_HX5.NPP3PER]>=[ALL_HX5.NPP4PER]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP2PER]>=[ALL_HX5.NPP3PER]) And ([ALL_HX5.NPP3PER]>=[ALL_HX5.NPP4PER]) And [NLIFE_STA]>=4,2,0)))) AS PPER
FROM ((((((PL5LHX4 INNER JOIN ALL_HXN ON (PL5LHX4.tPGM = ALL_HXN.tPGM) AND (PL5LHX4.nRACE = ALL_HXN.nRACE) AND (PL5LHX4.tDATE = ALL_HXN.tDATE) AND (PL5LHX4.tTRK = ALL_HXN.tTRK)) INNER JOIN ALL_HX9 ON (ALL_HXN.tPGM = ALL_HX9.tPGM) AND (ALL_HXN.nRACE = ALL_HX9.nRACE) AND (ALL_HXN.tTRK = ALL_HX9.tTRK) AND (ALL_HXN.tDATE = ALL_HX9.tDATE)) INNER JOIN ALL_HXV AS ALL_HXV_1 ON (ALL_HX9.tPGM = ALL_HXV_1.tPGM) AND (ALL_HX9.nRACE = ALL_HXV_1.nRACE) AND (ALL_HX9.tTRK = ALL_HXV_1.tTRACK) AND (ALL_HX9.tDATE = ALL_HXV_1.tDATE)) INNER JOIN PL4LHX4 ON (ALL_HXV_1.tPGM = PL4LHX4.tPGM) AND (ALL_HXV_1.nRACE = PL4LHX4.nRACE) AND (ALL_HXV_1.tDATE = PL4LHX4.tDATE) AND (ALL_HXV_1.tTRACK = PL4LHX4.tTRK)) INNER JOIN ALL_HX5 ON (PL4LHX4.tPGM = ALL_HX5.tPGM) AND (PL4LHX4.nRACE = ALL_HX5.nRACE) AND (PL4LHX4.tDATE = ALL_HX5.tDATE) AND (PL4LHX4.tTRK = ALL_HX5.tTRK)) INNER JOIN PL1L ON (ALL_HX5.tPGM = PL1L.tPGM) AND (ALL_HX5.nRACE = PL1L.nRACE) AND (ALL_HX5.tDATE = PL1L.tDATE) AND (ALL_HX5.tTRK = PL1L.tTRK)) INNER JOIN ALL_HX44 ON (PL1L.tPGM = ALL_HX44.tPGM) AND (PL1L.nRACE = ALL_HX44.nRACE) AND (PL1L.tDATE = ALL_HX44.tDATE) AND (PL1L.tTRK = ALL_HX44.tTRK);