And exactly as I said, you have aliased qryClaimDetails_C.COVERAGE as
COV and then tried to use that alias elsewhere in the query. Access
will sometimes allow you to do that in the SELECT clause, but will not
allow that alias to be used in a WHERE (or HAVING) clause or in an ORDER
BY clause.
Your best bet is to replace COV with Coverage as long as you don't have
a field named coverage in queryClaimDetails_A. OR Alias Coverage as COV
in the query qryClaimDetails_C.
You've done the same with Incurred which is dependent on Coverage.
The easiest way to work this out may be to generate another intermediate
query with Incurred calculated in the intermediate query and then use
that as the source to do all those convoluted IIF expressions.
By the way I don't see any reason for you to use a Group by clause. You
are not using any of the aggregate functions in this query.
SELECT DISTINCT
qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE AS COV
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL
, qryClaimDetails_A.SumOfPDLOSS AS PDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP AS PDO
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG
, IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred
============== REMOVE THIS ENTIRE SECTION ============================
============== Add it back in a query based on this query ==========
======================================================================
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF
, IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY
, Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF
, Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0))
,Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL
, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0))) AS
EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))
,Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0))) AS
EXPL_PROP
, 0 AS MEM1_GL
, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL
, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL
, IIf([COV]="EPL" And Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0)
AS MEM1_EPL, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0)
And Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)
-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0)) AS
MHAP_EPL
, IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0)
,IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>=2000000,2000000-
Nz([LIABRET],0),0)) AS EXLL_EPL, IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL
, IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0) AS PropGndUp
, IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)
+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0) AS AutoPhyGndUp
, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0) AS OtherGndUp,
[PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP
============== REMOVE THIS ENTIRE SECTION ============================
, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A
RIGHT JOIN qryClaimDetails_C
ON qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
================ DROP THIS ENTIRE BIT ====================
GROUP BY qryClaimDetails_C.[CLAIM#]
, qryClaimDetails_C.DACC
, qryClaimDetails_C.POLYR
, qryClaimDetails_C.COVERAGE
, qryClaimDetails_C.SUBIND
, qryClaimDetails_C.HA
, qryClaimDetails_C.HANAME
, qryClaimDetails_C.DESCR
, qryClaimDetails_C.STATUS
, qryClaimDetails_C.DRPT
, qryClaimDetails_C.CLAIMANT
, qryClaimDetails_A.SumOfPDLEGAL
, qryClaimDetails_A.SumOfPDLOSS
, qryClaimDetails_A.SumOfPDOTHEREXP
, qryClaimDetails_C.CURRES
, qryClaimDetails_C.DCURRES
, qryClaimDetails_C.RESCHG
, qryClaimDetails_C.RECNO
, qryClaimDetails_C.MEMDED
, qryClaimDetails_C.PROPRET
, qryClaimDetails_C.PROPAGG
, qryClaimDetails_C.AUTOPHYDED
, qryClaimDetails_C.TFDED
, qryClaimDetails_C.LIABRET
, qryClaimDetails_C.LIABAGG
, qryClaimDetails_C.LIABLIM
, qryClaimDetails_C.EPLDED
, qryClaimDetails_C.MHAPTFDED
, qryClaimDetails_C.MHAPDPLDED
, qryClaimDetails_C.MHAPDED
================ DROP THIS ENTIRE BIT ====================
=================Change the Having clause to a WHERE clause
===========================================================
WHERE (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0)
,IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))
ORDER BY qryClaimDetails_C.DACC DESC
, qryClaimDetails_C.SUBIND;
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
This is a huge query ... hope this goes!
SELECT qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE AS COV,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL AS PDLEGAL,
qryClaimDetails_A.SumOfPDLOSS AS PDLOSS,
qryClaimDetails_A.SumOfPDOTHEREXP AS PDO, qryClaimDetails_C.CURRES,
qryClaimDetails_C.DCURRES, qryClaimDetails_C.RESCHG, IIf([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0)))
AS Incurred, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>0,Nz([MEMDED],0),0) AS MEMDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=Nz([TFDED],0),Nz([TFDED],0),0) AS MEMDED_TF,
IIf(Nz([Incurred],0)>0 And Nz([COV],0)="AUTOPHY",Nz([AUTOPHYDED],0),0)
AS MEMDED_AUTOPHY,
Nz([MEMDED_PROP],0)+Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0) AS
MEMDED_TOTAL, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)<Nz([MHAPDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF",Nz([MHAPDED],0),0)) AS MHAPDED_PROP,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)<Nz([MHAPTFDED],0),Nz([Incurred],0),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF",Nz([MHAPTFDED],0),0)) AS MHAPDED_TF,
Nz([MHAPDED_PROP],0)+Nz([MHAPDED_TF],0) AS MHAPDED_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([TFDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([TFDED],0)-Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=((Nz([TFDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([MEMDED],0)+Nz([MHAPDED],0)) And
Nz([Incurred],0)<=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([Incurred],0)-(Nz([MEMDED],0)+Nz([MHAPDED],0)),IIf(Nz([COV],0)="PROP"
And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=((Nz([MEMDED],0)+Nz([MHAPDED],0))+Nz([PROPRET],0)),Nz([PROPRET],0),0))
AS PLSA_PROP, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_TOTAL,
IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)="TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([TFDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([TFDED],0)))
AS EXPL_TF, IIf(Nz([COV],0)="PROP" And Nz([SUBIND],0)<>"TF" And
Nz([Incurred],0)>=(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)),Nz([Incurred],0)-(Nz([PROPRET],0)+Nz([MHAPDED],0)+Nz([MEMDED],0)))
AS EXPL_PROP, 0 AS MEM1_GL, IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or
[COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0),0) AS MHAP_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(([COV]="GENL"
Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L") And
Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_GL,
IIf(([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="S8L")
And
Nz([Incurred],0)>(2000000+Nz([LIABRET],0)),Nz([Incurred],0)-2000000,0)
AS MEM2_GL, IIf([COV]="EPL" And
Nz([Incurred],0)>=Nz([EPLDED],0),Nz([EPLDED],0),0) AS MEM1_EPL,
IIf(Nz([COV],0)="EPL" And Nz([Incurred],0)>Nz([EPLDED],0) And
Nz([Incurred],0)<=Nz([LIABRET],0),Nz([Incurred],0)-Nz([EPLDED],0),IIf(Nz([COV],0)="EPL"
And
Nz([Incurred],0)>=Nz([LIABRET],0),Nz([LIABRET],0)-Nz([EPLDED],0),0))
AS MHAP_EPL, IIf(Nz([COV],0)="EPL" And
Nz([Incurred],0)>Nz([LIABRET],0) And
Nz([Incurred],0)<=2000000,Nz([Incurred],0)-Nz([LIABRET],0),IIf(Nz([COV],0)="EPL"
And Nz([Incurred],0)>=2000000,2000000-Nz([LIABRET],0),0)) AS EXLL_EPL,
IIf([COV]="EPL" And
Nz([Incurred],0)>2000000,Nz([Incurred],0)-2000000,0) AS MEM2_EPL,
IIf([COV]="PROP",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_PROP],0),0)
AS PropGndUp,
IIf([COV]="AUTOPHY",Nz([PDLEGAL],0)+Nz([PDLOSS],0)+Nz([PDO],0)+Nz([CURRES],0)+Nz([MEMDED_AUTOPHY],0),0)
AS AutoPhyGndUp, IIf([COV]<>"PROP" And [COV]<>"AUTOPHY",[Incurred],0)
AS OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP,
qryClaimDetails_C.RECNO, qryClaimDetails_C.MEMDED,
qryClaimDetails_C.PROPRET, qryClaimDetails_C.PROPAGG,
qryClaimDetails_C.AUTOPHYDED, qryClaimDetails_C.TFDED,
qryClaimDetails_C.LIABRET, qryClaimDetails_C.LIABAGG,
qryClaimDetails_C.LIABLIM, qryClaimDetails_C.EPLDED,
qryClaimDetails_C.MHAPTFDED, qryClaimDetails_C.MHAPDPLDED
FROM qryClaimDetails_A RIGHT JOIN qryClaimDetails_C ON
qryClaimDetails_A.[CLAIM#] = qryClaimDetails_C.[CLAIM#]
GROUP BY qryClaimDetails_C.[CLAIM#], qryClaimDetails_C.DACC,
qryClaimDetails_C.POLYR, qryClaimDetails_C.COVERAGE,
qryClaimDetails_C.SUBIND, qryClaimDetails_C.HA,
qryClaimDetails_C.HANAME, qryClaimDetails_C.DESCR,
qryClaimDetails_C.STATUS, qryClaimDetails_C.DRPT,
qryClaimDetails_C.CLAIMANT, qryClaimDetails_A.SumOfPDLEGAL,
qryClaimDetails_A.SumOfPDLOSS, qryClaimDetails_A.SumOfPDOTHEREXP,
qryClaimDetails_C.CURRES, qryClaimDetails_C.DCURRES,
qryClaimDetails_C.RESCHG, qryClaimDetails_C.RECNO,
qryClaimDetails_C.MEMDED, qryClaimDetails_C.PROPRET,
qryClaimDetails_C.PROPAGG, qryClaimDetails_C.AUTOPHYDED,
qryClaimDetails_C.TFDED, qryClaimDetails_C.LIABRET,
qryClaimDetails_C.LIABAGG, qryClaimDetails_C.LIABLIM,
qryClaimDetails_C.EPLDED, qryClaimDetails_C.MHAPTFDED,
qryClaimDetails_C.MHAPDPLDED, qryClaimDetails_C.MHAPDED
HAVING (((IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or
[COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+Nz([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDO],0),Nz([CURRES],0)+Nz([PDLOSS],0))))>500))
ORDER BY qryClaimDetails_C.DACC DESC , qryClaimDetails_C.SUBIND;
John said:
Can you post the SQL of the query?
If you are getting parameter prompts for the fields, that means
Access does not understand what those fields are. Normally they are
a misspelling or they don't exist in the tables that the query is using.
I think I misunderstood something you were saying, so ignore my
earlier response.