ACCESS 2016, too many fields defined error

Anonymous
2015-12-29T01:14:32+00:00

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

Microsoft 365 and Office | Access | For home | Windows

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.

0 comments
Answer accepted by question author
Anonymous
2017-12-22T00:15:56+00:00

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?

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-12-05T00:47:27+00:00

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.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-12-04T21:51:05+00:00

Passing on secret tip....

To format queries for easier reading, go to...

https://sqlformat.org/

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!

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2016-01-07T22:57:03+00:00

No problem... will get a message even if you reply 10 years from now! :)

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2015-12-29T01:41:09+00:00

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?

Was this answer helpful?

1 person found this answer helpful.
0 comments

64 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2017-12-17T17:22:46+00:00

    Im back. My earlier problem occurred because the company that sells me my data has a glitch in their time field. I just deleted it and everything worked.

    I took my report and made a new database. I have three queries in it and several tables. I took one of my queries L3AA and went through it and removed a lot of fields. Like you suggested. In another query L3A I just changed 4 fields and I got the error message to many fields defined. I thought that was odd because I just made a new database. So I had to delete out about 7 fields before the to many fields defined went away. I put the fields back in and made a new database. When I ran it i still got the error message to many fields defined. I made notes on how you told me to made a new database when i had the error to many fields defined. Below are those notes. They no longer work. Maybe I missed something. I know the Holidays are here and you quite busy, so whenever you get some free time please take a look at this for me.

    Thanks Gina

    ed

    My notes on making new database

    --OPEN ACCESS

    --CLICK ON BLANK DESKTOP DATA

    --YOULL SEE FILE NAME TYPE—DON’T CHANGE ANYTHING. YOU WILL SAVE THIS DB LATER.>CREATE

    --GO TO FILE >OPTIONS>CURRENT DATABASE AND UNCHECK TRACK NAME AUTOCORRECT INFO>OK

    THEN YOU WILL SOMETHING ABOUT TNAUTOCORRECT>YES

    --*****GO TO EXTERNAL DATA>NEW DATA SOURSE>FROM DATABSE>ACCESS>[NOTE THERE WILL BE A BULLET IN IMPORT TABLES, QUERIESFORMS, REPORT, MACROS, MODULE]--BROWSE UNTIL YOU FIND THE GAMBLER DATABASE YOU WANT TO COPY>CLICK ON IT—YOU WILL GO BACK TO BEGINNING AND SEE WERE YOU ARE GOING TO IMPORT FROM]>OK

    GO TO TABLE>SELECT ALL>OK>CLOSE

    --NOW GO TO QUERIES AND DO ****

    --GO TO FORMAT AND DO ******

    --GO TO FORM AND DO ********

    --NOW GO TO REPORT AND DO******

    --NOW GO TO MACRO AND DO ******

    --NOW GO TO MODULE AND DO ********

    --NOW GO TO EXTERNAL DATA> ACCESS. BROWSE TO FIND THE DATA BASE—SAME AS ***** GO TO TABLE AND SELECT OPTIONS..IN OPTIONS UNDER IMPORT SELECT EVERYTHING][RELATIONSHIPS—MENUD & TOOLBARS—IMPORT/EXPORT SPECS—NAVPANE GRP—ALL IMAGES & THEMS]—UNDER IMPORT TABLES DEFINITION AND DATA WILL ALREADY HAVE BULLET IN IT—UNDER IMPORT QUERIES AS QUERIES WILL ALREADY HAVE A BULLET IN IT[DON’T SELECT CHECK ALL]>OK

    NOW GO TO FORMS AND YOU HAVE YOUR NEW DATABASE AND TEMPLATE

    My query L3A SQL with the error message to many fields defined

    SELECT DISTINCT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nCLA, ALL_HX4.nPPO, ALL_HX4.tPGM, ALL_HX4.tHOR, ALL_HX4.nDIST, IIf(([ALL_HX4.TWK1] Like "[AB]*" And [ALL_HX4.TWK1] Like "*[567]*"),1,IIf(([ALL_HX4.TWK2] Like "[AB]*" And [ALL_HX4.TWK2] Like "*[567]*"),1,IIf(([ALL_HX4.TWK3] Like "[AB]*" And [ALL_HX4.TWK3] Like "*[567]*"),1,IIf(([ALL_HX4.TWK4] Like "[AB]*" And [ALL_HX4.TWK4] Like "*[567]*"),1,0)))) AS WWW, ALL_HX4.nAE, PL4LHX4.NPLS, PL5LHX4.nCLch, PL5LHX4.nFLD, PL5LHX4.nPURS, PL5LHX4.nQP, ALL_HX5.tPP1TRK, ALL_HX4.rTRN, ALL_HX4.rJKY, PL5LHX4.tTPG, Format(Val(Nz([PL5LHX4.TPGM],0)),"00000000") & Right(Nz([PL5LHX4.TPGM],0),1) AS PN, 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",IIf([PL5LHX4.NTRCH]=0,8,""))))))) AS TCC, ALL_HX4.nJKCH, ALL_HX5.nPP1FIN, ALL_HX5.nPP2FIN, ALL_HX5.nPP3FIN, PL5LHX4.SOR1, PL5LHX4.SOR2, PL5LHX4.SOR3, PL5LHX4.SF1, PL5LHX4.SF2, PL5LHX4.SF3, ALL_HXV.tPP2TRK, ALL_HXV.tPP3TRK, PL5LHX4.nK, PL5LHX4.SF4, ALL_HXV.tPP4TRK, ALL_HX4.tTRN, ALL_HX4.tJKY, ALL_HX9.[nTSTAT1_WN%], ALL_HX9.[nTSTAT8_WN%], PL5LHX4.nVI, PL5LHX4.nJKYHST, PL5LHX4.nJKYHW, PL5LHX4.nTRNHW, DateDiff("d",[PL4LHX4.tLdate],[twk1_date]) AS dd1, PL5LHX4.nRAZOR, ALL_HX9.[nTSTAT3_WN%], PL5LHX4.AGO2, PL5LHX4.AGO3, PL5LHX4.AGO1, PL5LHX4.nKAT, 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, PL4LHX4.nBUGBOY, IIf([ALL_HX4.NTRN_BUZZ]>=3,3,IIf([ALL_HX4.NTRN_BUZZ]>=1,1,0)) AS TBUZZ, [ALL_HX5.NPP1PER]-[PL5LHX4.NEPR] AS PPE1, [ALL_HX5.NPP2PER]-[PL5LHX4.NEPR] AS PPE2, [ALL_HX5.NPP3PER]-[PL5LHX4.NEPR] AS PPE3, IIf([ALL_HX5.NPP2PAC]>[ALL_HX5.NPP1PAC],[ALL_HX5.NPP2PAC],[ALL_HX5.NPP1PAC]) AS 2PA, IIf([ALL_HX5.NPP2PER]>[ALL_HX5.NPP1PER],[ALL_HX5.NPP2PER],[ALL_HX5.NPP1PER]) AS 2PE, PL5LHX4.rFr1, PL5LHX4.rLP, PL5LHX4.nFR1_DOM, PL5LHX4.nFR1_ADV, [2PA]-[PL5LHX4.NEPR_PAC] AS 2PAA, [2PE]-[PL5LHX4.NEPR] AS 2PEE, IIf([PPE1]>=[PPE2],IIf([PPE2]>=[PPE3],[PPE1]+[PPE2],[PPE1]+[PPE3]),IIf([PPE1]>=[PPE3],[PPE1]+[PPE2],[PPE2]+[PPE3])) AS TTT, IIf(([ALL_HXV.NPP1FR1]>=[ALL_HXV.NPP2FR1]) And ([ALL_HXV.NPP2FR1]>=[ALL_HXV.NPP3FR1]) And [NLIFE_STA]>2,3,IIf(([ALL_HXV.NPP1FR1]<=[ALL_HXV.NPP2FR1]) And [ALL_HXV.NPP2FR1]<=[ALL_HXV.NPP3FR1] And [NLIFE_STA]>2,2,0)) AS LF1, IIf(([ALL_HXV.NPP1LP]>=[ALL_HXV.NPP2LP]) And ([ALL_HXV.NPP2LP]>=[ALL_HXV.NPP3LP]) And [NLIFE_STA]>2,3,IIf(([ALL_HXV.NPP1LP]<=[ALL_HXV.NPP2LP]) And [ALL_HXV.NPP2LP]<=[ALL_HXV.NPP3LP] And [NLIFE_STA]>2,2,0)) AS LPP, ALL_HX4.nBLOWOUT, ALL_HXV.nPP1ODDS, [ALL_HX5.NPP1PAC]-[PL5LHX4.NEPR] AS PPA1, [ALL_HX5.NPP2PAC]-[PL5LHX4.NEPR] AS PPA2, [ALL_HX5.NPP3PAC]-[PL5LHX4.NEPR] AS PPA3, IIf([PPA1]>=[PPA2],IIf([PPA2]>=[PPA3],[PPA1]+[PPA2],[PPA1]+[PPA3]),IIf([PPA1]>=[PPA3],[PPA1]+[PPA2],[PPA2]+[PPA3])) AS PPP, ALL_HX5.n2CP1, ALL_HX5.n2CP2, [NPP1FR1]-60 AS FR11, [NPP2FR1]-60 AS FR12, [NPP3FR1]-60 AS FR13, ALL_HXV.nPP1FR1, ALL_HXV.nPP2FR1, ALL_HXV.nPP3FR1, ALL_HX5.n2CBL1, ALL_HX5.n2CBL2, ALL_HX5.n2CBL3, PL5LHX4.rK, ALL_HX5.NPP1SHF AS LP11, ALL_HX5.NPP2SHF AS LP12, ALL_HX5.NPP3SHF AS LP13, IIf(([ALL_HX5.NPP1SHF]>=[ALL_HX5.NPP2SHF]) And ([ALL_HX5.NPP2SHF]>=[ALL_HX5.NPP3SHF]) And [NLIFE_STA]>2,3,IIf(([ALL_HX5.NPP1SHF]<=[ALL_HX5.NPP2SHF]) And [ALL_HX5.NPP2SHF]<=[ALL_HX5.NPP3SHF] And [NLIFE_STA]>2,2,0)) AS ZZZ, ALL_HX44.nTURNS, PL5LHX4.nCOURSE, 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, 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, IIf([ALL_HX4.NWKSC]>=90,2,IIf([ALL_HX4.NWKSC]>=85,1,0)) AS WK, IIf([pl5lhx4.NPACE]=1,"LON SPD",IIf([PL5LHX4.NPACE]=2,"HOT",IIf([PL5LHX4.NPACE]=3,"FAST",IIf([PL5LHX4.NPACE]=4,"NORMAL",IIf([PL5LHX4.NPACE]=5,"SOFT",""))))) AS A11, ALL_HX5.n2CP3, [PPP]+[TTT] AS TTP, ALL_HX9.nTSTAT2_STR, PL5LHX4.rHTR, IIf(([LP11]<=[LP12]) And ([LP12]<=[LP13]) And [NLIFE_STA]>2,3,IIf(([LP11]>=[LP12]) And [LP12]>=[LP13] And [NLIFE_STA]>2,2,0)) AS SHF, IIf([PL5LHX4.NIMPROVE] Between 3 And 4,1,IIf([PL5LHX4.NIMPROVE] Between 5 And 15,3,0)) AS IMP, 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.NPP3PAC] And [NLIFE_STA]>=3,6,IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP2PAC]) And ([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP4PAC]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And [NLIFE_STA]>=4,2,0))))) AS PPAC, PL5LHX4.nIMPROVE, [ALL_HX5.NPP4PER]-[PL5LHX4.NEPR] AS PPE4, ALL_HX5.nPP4FIN, PL5LHX4.SOR4, ALL_HX44.NPOSTOT, ALL_HX44.RPOSTOT, 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,2,IIf(([ALL_HX5.NPP1PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP2PAC]>=[ALL_HX5.NPP3PAC]) And ([ALL_HX5.NPP3PAC]>=[ALL_HX5.NPP4PAC]) And [NLIFE_STA]>=4,2,IIf(([ALL_HX5.NPP1PAC]<[ALL_HX5.NPP2PAC]) And ([ALL_HX5.NPP2PAC]<[ALL_HX5.NPP3PAC]) And [NLIFE_STA]>=3,4,0))))) AS PEE, 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,IIf(([ALL_HX5.NPP1PER]<[ALL_HX5.NPP2PER]) And ([ALL_HX5.NPP2PER]<[ALL_HX5.NPP3PER]) And [NLIFE_STA]>=3,4,0))))) AS PPER, IIf([ALL_HX5.NFNBL1]>=10,1,IIf([ALL_HX5.NFNBL2]>=10,2,0)) AS UT, PL5LHX4.nTRNHST, ALL_HXV.nPP1AGO, ALL_HXV.nPP2AGO, ALL_HXV.nPP3AGO, ALL_HXV.nPP4AGO, PL4LHX4.CHG1, ALL_HX4.nLUCK, IIf(([PL4LHX4.NCHG]=1 Or [PL4LHX4.NCHG]=5 Or [PL4LHX4.NCHG]=4 Or [PL4LHX4.NCHG]=6) And [ALL_HX5.TPP1TRK]<>[ALL_HX5.TTRK],1,0) AS CHGL, ALL_HX4.nOUTLOOK, [NTOPPER]-[PL5LHX4.NEPR] AS PERTOP, IIf([PL5LHX4.NOUTLOOK]=1,"1VCHAULK",IIf([PL5LHX4.NOUTLOOK]=2,"2CHAULK",IIf([PL5LHX4.NOUTLOOK]=3,"3MIXED",IIf([PL5LHX4.NOUTLOOK]=4,"4VALUE",IIf([PL5LHX4.NOUTLOOK]=5,"5WOPEN",IIf([PL5LHX4.NOUTLOOK]=6,"6BOMBS","")))))) AS OLOOK, ALL_HX44.rMLO, ALL_HX44.tPOSSCAN, IIf([ALL_HX4.NPPO]>0,[ALL_HXD.tRACECOND],0) AS RACE, PL5LHX4.DIST11, PL5LHX4.DIST22, PL5LHX4.DIST33, PL5LHX4.DIST44, IIf([ALL_HX4.NDIST]<8 And [NPP1DIS]<8,1,IIf([ALL_HX4.NDIST]>=8 And [NPP1DIS]>=8,1,0)) AS PAD1, IIf([ALL_HX4.NDIST]<8 And [NPP2DIS]<8,1,IIf([ALL_HX4.NDIST]>=8 And [NPP2DIS]>=8,1,0)) AS PAD2, IIf([ALL_HX4.NDIST]<8 And [NPP3DIS]<8,1,IIf([ALL_HX4.NDIST]>=8 And [NPP3DIS]>=8,1,0)) AS PAD3, IIf([ALL_HX4.NDIST]<8 And [NPP4DIS]<8,1,IIf([ALL_HX4.NDIST]>=8 And [NPP4DIS]>=8,1,0)) AS PAD4, PL1L.tRACECOND, ALL_HX4.nHAG, ([NPP1FR1]-50)*10+20 AS F1, ([NPP2FR1]-50)*10+20 AS F2, ([NPP3FR1]-50)*10+20 AS F3, ([NPP4FR1]-50)*10+20 AS F4, PL1L.tCONDS, ALL_HX44.nTR_TDS_STR, ALL_HX44.nJK_TDS_STR, ALL_HX9.[nTSTAT2_WN%], ALL_HX44.nTR_TDS_WNP, ALL_HX44.nJK_TDS_WNP, ALL_HXV.nPP2FSZ, ALL_HXV.nPP3FSZ, ALL_HXV.nPP4FSZ, ALL_HXV.nPP1FSZ, IIf(([ALL_HXV.NPP1CSF]>=[ALL_HXV.NPP2CSF]) And ([ALL_HXV.NPP2CSF]>=[ALL_HXV.NPP3CSF]) And [NLIFE_STA]>=3,3,IIf(([ALL_HXV.NPP1CSF]>=[ALL_HXV.NPP2CSF]) And ([ALL_HXV.NPP2CSF]>=[ALL_HXV.NPP4CSF]) And [NLIFE_STA]>=4,2,IIf(([ALL_HXV.NPP1CSF]>=[ALL_HXV.NPP3CSF]) And ([ALL_HXV.NPP3CSF]>=[ALL_HXV.NPP4CSF]) And [NLIFE_STA]>=4,2,IIf(([ALL_HXV.NPP2CSF]>=[ALL_HXV.NPP3CSF]) And ([ALL_HXV.NPP3CSF]>=[ALL_HXV.NPP4CSF]) And [NLIFE_STA]>=4,2,IIf(([ALL_HXV.NPP1CSF]<[ALL_HXV.NPP2CSF]) And ([ALL_HXV.NPP2CSF]<[ALL_HXV.NPP3CSF]) And [NLIFE_STA]>=3,4,0))))) AS CSFF, ALL_HX5.nFNBL1, ALL_HX5.nFNBL2, ALL_HX5.nFNBL3, ALL_HX5.nFNBL4, ALL_HX4.nC90, ALL_HX4.nPfig, ALL_HX4.rPfig, ALL_HX4.rC90, ALL_HXV.nPP2CSF, ALL_HXV.nPP3CSF, ALL_HXV.nPP4CSF, (50-[NPFIG])*2.5 AS PCSF, ALL_HX4.nTOPPERAGO, ALL_HX5.nPP1DIS, ALL_HX5.nPP2DIS, ALL_HX5.nPP3DIS, ALL_HX5.nPP4DIS, PL4LHX4.rK2, ALL_HX44.nSYS4, ALL_HX44.rSYS4, ALL_HX4.nPSCN, ALL_HX4.rPSCN, PL5LHX4.tWK1, PL5LHX4.tWK2, PL5LHX4.tWK3, PL5LHX4.tWK4, ALL_HX9.tWK1_TRACK, ALL_HX9.tWK2_TRACK, ALL_HX9.tWK3_TRACK, ALL_HX9.tWK4_TRACK, ALL_HX4.nWKSC, ALL_HX4.rWKSC, PL4LHX4.TRC, IIf(([WWW]=1 Or [F60] Between 1 And 3) And [ALL_HX4.NTRCH] Between 1 And 6,1,0) AS TTCH, PL4LHX4.SORT, PL4LHX4.tPLSELECTED, ALL_HX44.nJK365_Starts, ALL_HX44.nJK365_WinP, ALL_HX44.nTR365_Starts, ALL_HX44.nTR365_WinP, PL5LHX4.F60, ALL_HX44.rSYS2, ALL_HX44.rSYS3, ALL_HX44.rSYS1, [NN06]+[NN07]+[NN09]+[NN10]+[NN11]+[NN12]+[NN13] AS PT, [NN05]+[NN15] AS PJ, ALL_HXN.nB22, [NPOS14]+[NPOS16]+[NB16]+[NB17] AS GJ, [NPOS30]+[NPOS32]+[NB01]+[NB12]+[NB13]+[NB22] AS GT, IIf([NB14]=1 Or [NB15]=1,1,IIf([NN08]=1,2,0)) AS TJ, ALL_HX4.nK2, [NPLS]+[PPER]+[LPP]+[LT]+[NPOS04]+[NPOS13]+[NPOS15]+[NPOS24]+[NPOS29]+[NPOS31]+[NPOS36]+[NB01]+[NB10]+[NB11]+[NB12]+[NB13]+[NB14]+[NB15]+[NB16]+[NB17]+[NB20]+[NB21]+[NB22] AS NPLS1, IIf(Val([ALL_HX4.TPGM])=[ALL_HX4.NPPO],[ALL_HX4.NBARN],[ALL_HX4.NPPO]) AS BARN22, [NTRACK_WIN]+[NTRACK_PLA]+[NTRACK_SHO] AS TSITM, ALL_HX9.nLIFE_STA, PL1L.BLSUM, ALL_HXV.nPP1CSF, ALL_HX9.nDIST_STA, IIf([NDIST_STA]=0,0,[NDIST_WIN]+[NDIST_PLA]+[NDIST_SHO]) AS HDSITM, ALL_HX9.nTRACK_STA, IIf([ALL_HX5.NPP1FIN]<=(1/2*[ALL_HXV.NPP1FSZ]) And ([NLFST]>=1) And ([ALL_HX5.NPP1PAC]>=[ALL_HXV.NPP1RPAC]-2),1,IIf([ALL_HX5.NPP1FIN]<=(1/2*[ALL_HXV.NPP1FSZ]) And ([NLFST]>=1) And [ALL_HX5.NPP1PER]>=([ALL_HXV.NPP1RPER]-3),2,0)) AS LIV, IIf([ALL_HX5.NPP2FIN]<=(1/2*[ALL_HXV.NPP2FSZ]) And ([NLFST]>=2) And ([ALL_HX5.NPP2PAC]>=[ALL_HXV.NPP2RPAC]-2),1,IIf([ALL_HX5.NPP2FIN]<=(1/2*[ALL_HXV.NPP2FSZ]) And ([NLFST]>=2) And [ALL_HX5.NPP2PER]>=([ALL_HXV.NPP2RPER]-3),2,0)) AS LIV1, IIf([ALL_HX5.NPP3FIN]<=(1/2*[ALL_HXV.NPP3FSZ]) And ([NLFST]>=3) And ([ALL_HX5.NPP3PAC]>=[ALL_HXV.NPP3RPAC]-2),1,IIf([ALL_HX5.NPP3FIN]<=(1/2*[ALL_HXV.NPP3FSZ]) And ([NLFST]>=3) And [ALL_HX5.NPP3PER]>=([ALL_HXV.NPP3RPER]-3),2,0)) AS LIV2, IIf([ALL_HX5.NPP4FIN]<=(1/2*[ALL_HXV.NPP4FSZ]) And ([NLFST]>=4) And ([ALL_HX5.NPP4PAC]>=[ALL_HXV.NPP4RPAC]-2),1,IIf([ALL_HX5.NPP4FIN]<=(1/2*[ALL_HXV.NPP4FSZ]) And ([NLFST]>=4) And [ALL_HX5.NPP4PER]>=([ALL_HXV.NPP4RPER]-3),2,0)) AS LIV3, PL5LHX4.LT

    FROM ((((ALL_HX4 INNER JOIN PL4LHX4 ON (ALL_HX4.nRACE = PL4LHX4.nRACE) AND (ALL_HX4.tDATE = PL4LHX4.tDATE) AND (ALL_HX4.tTRK = PL4LHX4.tTRK) AND (ALL_HX4.tPGM = PL4LHX4.tPGM)) INNER JOIN ((((PL5LHX4 INNER JOIN ALL_HX5 ON (PL5LHX4.tTRK = ALL_HX5.tTRK) AND (PL5LHX4.tDATE = ALL_HX5.tDATE) AND (PL5LHX4.nRACE = ALL_HX5.nRACE) AND (PL5LHX4.tPGM = ALL_HX5.tPGM)) INNER JOIN ALL_HXN ON (ALL_HX5.tTRK = ALL_HXN.tTRK) AND (ALL_HX5.tDATE = ALL_HXN.tDATE) AND (ALL_HX5.nRACE = ALL_HXN.nRACE) AND (ALL_HX5.tPGM = ALL_HXN.tPGM)) INNER JOIN ALL_HXD ON (ALL_HXN.tPGM = ALL_HXD.tPGM) AND (ALL_HXN.nRACE = ALL_HXD.nRACE) AND (ALL_HXN.tTRK = ALL_HXD.tTRK) AND (ALL_HXN.tDATE = ALL_HXD.tDATE)) INNER JOIN ALL_HXV ON (ALL_HXD.tPGM = ALL_HXV.tPGM) AND (ALL_HXD.nRACE = ALL_HXV.nRACE) AND (ALL_HXD.tDATE = ALL_HXV.tDATE) AND (ALL_HXD.tTRK = ALL_HXV.tTRACK)) ON (PL4LHX4.nRACE = PL5LHX4.nRACE) AND (PL4LHX4.tDATE = PL5LHX4.tDATE) AND (PL4LHX4.tTRK = PL5LHX4.tTRK) AND (PL4LHX4.tPGM = PL5LHX4.tPGM)) INNER JOIN PL1L ON (ALL_HXV.tPGM = PL1L.tPGM) AND (ALL_HXV.nRACE = PL1L.nRACE) AND (ALL_HXV.tDATE = PL1L.tDATE) AND (ALL_HXV.tTRACK = PL1L.tTRK)) INNER JOIN ALL_HX9 ON (PL1L.tPGM = ALL_HX9.tPGM) AND (PL1L.nRACE = ALL_HX9.nRACE) AND (PL1L.tDATE = ALL_HX9.tDATE) AND (PL1L.tTRK = ALL_HX9.tTRK)) INNER JOIN ALL_HX44 ON (ALL_HX9.tDATE = ALL_HX44.tDATE) AND (ALL_HX9.tTRK = ALL_HX44.tTRK) AND (ALL_HX9.nRACE = ALL_HX44.nRACE) AND (ALL_HX9.tPGM = ALL_HX44.tPGM);

    Was this answer helpful?

    0 comments