is there a better way?

[Gary B]

Is there a better way? There are so many iif() statements. By the way, the
NZ() statement fails... you see, I run these querys from an outside
application through Jet and ADO. Also, for the same reasons, I need to pass
the parameter as Text, then convert it, otherwise, the returned results are
skewed.

AgingSummary:

PARAMETERS DATETO Text ( 22 );
SELECT PATIENTS.PATNO, (PATIENTS.LNAME+", "+PATIENTS.FNAME) AS PATNAME,
iif(IsNull(AgingSummary_Charges.CURPATCHRG), 0,
AgingSummary_Charges.CURPATCHRG)-iif(IsNull(AgingSummary_Payments.CURPATPAID),
0, AgingSummary_Payments.CURPATPAID) AS CURPAT,
iif(IsNull(AgingSummary_Charges.CURINSCHRG), 0,
AgingSummary_Charges.CURINSCHRG)-iif(IsNull(AgingSummary_Payments.CURINSPAID),
0, AgingSummary_Payments.CURINSPAID) AS CURINS,
iif(IsNull(AgingSummary_Charges.THIRPATCHRG), 0,
AgingSummary_Charges.THIRPATCHRG)-iif(IsNull(AgingSummary_Payments.THIRPATPAID),
0, AgingSummary_Payments.THIRPATPAID) AS THIRPAT,
iif(IsNull(AgingSummary_Charges.THIRINSCHRG), 0,
AgingSummary_Charges.THIRINSCHRG)-iif(IsNull(AgingSummary_Payments.THIRINSPAID),
0, AgingSummary_Payments.THIRINSPAID) AS THIRINS,
iif(IsNull(AgingSummary_Charges.SIXPATCHRG), 0,
AgingSummary_Charges.SIXPATCHRG)-iif(IsNull(AgingSummary_Payments.SIXPATPAID),
0, AgingSummary_Payments.SIXPATPAID) AS SIXPAT,
iif(IsNull(AgingSummary_Charges.SIXINSCHRG), 0,
AgingSummary_Charges.SIXINSCHRG)-iif(IsNull(AgingSummary_Payments.SIXINSPAID),
0, AgingSummary_Payments.SIXINSPAID) AS SIXINS,
iif(IsNull(AgingSummary_Charges.NINEPATCHRG),
0,AgingSummary_Charges.NINEPATCHRG)-iif(IsNull(AgingSummary_Payments.NINEPATPAID),
0,AgingSummary_Payments.NINEPATPAID) AS NINEPAT,
iif(IsNull(AgingSummary_Charges.NINEINSCHRG),
0,AgingSummary_Charges.NINEINSCHRG)-iif(IsNull(AgingSummary_Payments.NINEINSPAID),
0,AgingSummary_Payments.NINEINSPAID) AS NINEINS,
iif(IsNull(AgingSummary_Charges.TOTALPATCHRG), 0,
AgingSummary_Charges.TOTALPATCHRG)-iif(IsNull(AgingSummary_Payments.TOTALPATPAID),
0, AgingSummary_Payments.TOTALPATPAID) AS TOTALPAT,
iif(IsNull(AgingSummary_Charges.TOTALINSCHRG), 0,
AgingSummary_Charges.TOTALINSCHRG)-iif(IsNull(AgingSummary_Payments.TOTALINSPAID),
0, AgingSummary_Payments.TOTALINSPAID) AS TOTALINS
FROM (PATIENTS LEFT JOIN AgingSummary_Charges ON PATIENTS.PATIENTKEY =
AgingSummary_Charges.PATIENTKEY) LEFT JOIN AgingSummary_Payments ON
PATIENTS.PATIENTKEY = AgingSummary_Payments.PATIENTKEY
WHERE ( iif(IsNull(AgingSummary_Charges.TOTALPATCHRG), 0,
AgingSummary_Charges.TOTALPATCHRG) <>
iif(IsNull(AgingSummary_Payments.TOTALPATPAID), 0,
AgingSummary_Payments.TOTALPATPAID) ) or
( iif(IsNull(AgingSummary_Charges.TOTALINSCHRG), 0,
AgingSummary_Charges.TOTALINSCHRG) <>
iif(IsNull(AgingSummary_Payments.TOTALINSPAID), 0,
AgingSummary_Payments.TOTALINSPAID) )
ORDER BY PATIENTS.LNAME, PATIENTS.FNAME

AgingSummary_Charges:

SELECT PATCHARGES.PATIENTKEY, Sum(IIf(PATCHARGES.DATE Between
DateValue(DATETO)-30 And DateValue(DATETO)-0,PATSERVICES.PATCHRG,0)) AS
CURPATCHRG,
Sum(IIf(PATCHARGES.DATE Between DateValue(DATETO)-30 And
DateValue(DATETO)-0,PATSERVICES.INSCHRG,0)) AS CURINSCHRG,
Sum(IIf(PATCHARGES.DATE Between DateValue(DATETO)-60 And
DateValue(DATETO)-31,PATSERVICES.PATCHRG,0)) AS THIRPATCHRG,
Sum(IIf(PATCHARGES.DATE Between DateValue(DATETO)-60 And
DateValue(DATETO)-31,PATSERVICES.INSCHRG,0)) AS THIRINSCHRG,
Sum(IIf(PATCHARGES.DATE Between DateValue(DATETO)-90 And
DateValue(DATETO)-61,PATSERVICES.PATCHRG,0)) AS SIXPATCHRG,
Sum(IIf(PATCHARGES.DATE Between DateValue(DATETO)-90 And
DateValue(DATETO)-61,PATSERVICES.INSCHRG,0)) AS SIXINSCHRG,
Sum(IIf(PATCHARGES.DATE<=DateValue(DATETO)-91,PATSERVICES.PATCHRG,0)) AS
NINEPATCHRG,
Sum(IIf(PATCHARGES.DATE<=DateValue(DATETO)-91,PATSERVICES.INSCHRG,0)) AS
NINEINSCHRG,
(CURPATCHRG+THIRPATCHRG+SIXPATCHRG+NINEPATCHRG) AS TOTALPATCHRG,
(CURINSCHRG+THIRINSCHRG+SIXINSCHRG+NINEINSCHRG) AS TOTALINSCHRG
FROM PATCHARGES INNER JOIN PATSERVICES ON PATCHARGES.PATCHARGESKEY =
PATSERVICES.PATCHARGESKEY
GROUP BY PATCHARGES.PATIENTKEY;


AgingSummary_Payments:

SELECT PATPAYMENTS.PATIENTKEY, Sum(iif(PATPAYMENTS.DATE Between
DateValue(DATETO)-30 and DateValue(DATETO)-00 And PATPAYMENTS.TYPE=1,
PATPAYMENTS.AMOUNT, 0)) AS CURPATPAID,
Sum(iif(PATPAYMENTS.DATE Between DateValue(DATETO)-30 and
DateValue(DATETO)-00 And PATPAYMENTS.TYPE=2, PATPAYMENTS.AMOUNT, 0)) AS
CURINSPAID,
Sum(iif(PATPAYMENTS.DATE Between DateValue(DATETO)-60 and
DateValue(DATETO)-31 And PATPAYMENTS.TYPE=1, PATPAYMENTS.AMOUNT, 0)) AS
THIRPATPAID,
Sum(iif(PATPAYMENTS.DATE Between DateValue(DATETO)-60 and
DateValue(DATETO)-31 And PATPAYMENTS.TYPE=2, PATPAYMENTS.AMOUNT, 0)) AS
THIRINSPAID,
Sum(iif(PATPAYMENTS.DATE Between DateValue(DATETO)-90 and
DateValue(DATETO)-61 And PATPAYMENTS.TYPE=1, PATPAYMENTS.AMOUNT, 0)) AS
SIXPATPAID,
Sum(iif(PATPAYMENTS.DATE Between DateValue(DATETO)-90 and
DateValue(DATETO)-61 And PATPAYMENTS.TYPE=2, PATPAYMENTS.AMOUNT, 0)) AS
SIXINSPAID,
Sum(iif(PATPAYMENTS.DATE <= DateValue(DATETO)-91 And PATPAYMENTS.TYPE=1,
PATPAYMENTS.AMOUNT, 0)) AS NINEPATPAID,
Sum(iif(PATPAYMENTS.DATE <= DateValue(DATETO)-91 And PATPAYMENTS.TYPE=2,
PATPAYMENTS.AMOUNT, 0)) AS NINEINSPAID,
(CURPATPAID+THIRPATPAID+SIXPATPAID+NINEPATPAID) AS TOTALPATPAID,
(CURINSPAID+THIRINSPAID+SIXINSPAID+NINEINSPAID) AS TOTALINSPAID
FROM PATPAYMENTS
WHERE (PATPAYMENTS.PAY_METHODSKEY Not Between 3 And 6)
GROUP BY PATPAYMENTS.PATIENTKEY;

 

[[MVP] S.Clark]

Very seldom does the answer to any query question contain the IIF() clause.
It's about the worst thing you can use. They're slow, difficult to debug,
and error prone.

So, to answer your question, there is probably a better way.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Gary B]

Then what is the better way... Help!

 

[[MVP] S.Clark]

The first query could benefit from the Nz() function, instead of the IIF.

The second and third queries could be broken down, each into smaller
queries, one for each criteria need, then the data combined into a final
query. Or else use a series of append queries to write data to a temp
table, then use the temp table as a basis for reports or whatnots.

Or just leave it the way it is, and hope it never breaks or needs updated.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting