No Current Record in Query!!! 1

[Freckles]

The No current Record problem seems to be related to the type of JOIN, but I can not get the data I want when I use the inner join. AND, if I take out the information about the Duplicate record (see bold) I also get records.

The following returns NO CURRENT RECORD

SELECT [REF_Dashboard Base].dashboard_rpt, [REF_Dashboard Base].dashboard_rpt_order, UNIT_REVENUE.VIEW, Sum(UNIT_REVENUE.jan_is) AS JAN03_IS, Sum(UNIT_REVENUE.feb_is) AS FEB03_IS, Sum(UNIT_REVENUE.mar_is) AS MAR03_IS, Sum(UNIT_REVENUE.apr_is) AS APR03_IS, Sum(UNIT_REVENUE.may_is) AS MAY03_IS, Sum(UNIT_REVENUE.jun_is) AS JUN03_IS, Sum(UNIT_REVENUE.jul_is) AS JUL03_IS, Sum(UNIT_REVENUE.aug_is) AS AUG03_IS, Sum(UNIT_REVENUE.sep_is) AS SEP03_IS, Sum(UNIT_REVENUE.oct_is) AS OCT03_IS, Sum(UNIT_REVENUE.nov_is) AS NOV03_IS, Sum(UNIT_REVENUE.dec_is) AS DEC03_IS, Sum(+[jan_is]+[feb_is]+[mar_is]+[apr_is]+[may_is]+[jun_is]+[jul_is]+[aug_is]+[sep_is]+[oct_is]+[nov_is]+[dec_is]) AS [Total IS], Sum(UNIT_REVENUE.YTD_03_IS) AS SumOfYTD_03_IS, Sum(UNIT_REVENUE.CUR_MO_03_IS) AS SumOfCUR_MO_03_IS, UNIT_REVENUE.[Duplicate?]
FROM UNIT_REVENUE RIGHT JOIN [REF_Dashboard Base] ON (UNIT_REVENUE.[DASHBOARD SO] = [REF_Dashboard Base].dashboard_rpt_order) AND (UNIT_REVENUE.DASHBOARD = [REF_Dashboard Base].dashboard_rpt)
GROUP BY [REF_Dashboard Base].dashboard_rpt, [REF_Dashboard Base].dashboard_rpt_order, UNIT_REVENUE.VIEW, UNIT_REVENUE.[Duplicate?]
HAVING (((UNIT_REVENUE.VIEW)="2003ACT" Or (UNIT_REVENUE.VIEW) Is Null) AND ((UNIT_REVENUE.[Duplicate?]) Is Null Or (UNIT_REVENUE.[Duplicate?])=No))
ORDER BY [REF_Dashboard Base].dashboard_rpt_order;

The following returns records:

SELECT [REF_Dashboard Base].dashboard_rpt, [REF_Dashboard Base].dashboard_rpt_order, UNIT_REVENUE.VIEW, Sum(UNIT_REVENUE.jan_is) AS JAN03_IS, Sum(UNIT_REVENUE.feb_is) AS FEB03_IS, Sum(UNIT_REVENUE.mar_is) AS MAR03_IS, Sum(UNIT_REVENUE.apr_is) AS APR03_IS, Sum(UNIT_REVENUE.may_is) AS MAY03_IS, Sum(UNIT_REVENUE.jun_is) AS JUN03_IS, Sum(UNIT_REVENUE.jul_is) AS JUL03_IS, Sum(UNIT_REVENUE.aug_is) AS AUG03_IS, Sum(UNIT_REVENUE.sep_is) AS SEP03_IS, Sum(UNIT_REVENUE.oct_is) AS OCT03_IS, Sum(UNIT_REVENUE.nov_is) AS NOV03_IS, Sum(UNIT_REVENUE.dec_is) AS DEC03_IS, Sum(+[jan_is]+[feb_is]+[mar_is]+[apr_is]+[may_is]+[jun_is]+[jul_is]+[aug_is]+[sep_is]+[oct_is]+[nov_is]+[dec_is]) AS [Total IS], Sum(UNIT_REVENUE.YTD_03_IS) AS SumOfYTD_03_IS, Sum(UNIT_REVENUE.CUR_MO_03_IS) AS SumOfCUR_MO_03_IS, UNIT_REVENUE.[Duplicate?]
FROM UNIT_REVENUE INNER JOIN [REF_Dashboard Base] ON (UNIT_REVENUE.[DASHBOARD SO] = [REF_Dashboard Base].dashboard_rpt_order) AND (UNIT_REVENUE.DASHBOARD = [REF_Dashboard Base].dashboard_rpt)
GROUP BY [REF_Dashboard Base].dashboard_rpt, [REF_Dashboard Base].dashboard_rpt_order, UNIT_REVENUE.VIEW, UNIT_REVENUE.[Duplicate?]
HAVING (((UNIT_REVENUE.VIEW)="2003ACT" Or (UNIT_REVENUE.VIEW) Is Null) AND ((UNIT_REVENUE.[Duplicate?]) Is Null Or (UNIT_REVENUE.[Duplicate?])=No))
ORDER BY [REF_Dashboard Base].dashboard_rpt_order;


: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Golom]

Just reformatted a bit
[blue][tt]
SELECT D.dashboard_rpt, D.dashboard_rpt_order, U.VIEW,
Sum(U.jan_is) AS JAN03_IS,
Sum(U.feb_is) AS FEB03_IS,
Sum(U.mar_is) AS MAR03_IS,
Sum(U.apr_is) AS APR03_IS,
Sum(U.may_is) AS MAY03_IS,
Sum(U.jun_is) AS JUN03_IS,
Sum(U.jul_is) AS JUL03_IS,
Sum(U.aug_is) AS AUG03_IS,
Sum(U.sep_is) AS SEP03_IS,
Sum(U.oct_is) AS OCT03_IS,
Sum(U.nov_is) AS NOV03_IS,
Sum(U.dec_is) AS DEC03_IS,
Sum(+[jan_is]+[feb_is]+[mar_is]+[apr_is]+[may_is]+[jun_is]+[jul_is]+
[aug_is]+[sep_is]+[oct_is]+[nov_is]+[dec_is]) AS [Total IS],
Sum(U.YTD_03_IS) AS SumOfYTD_03_IS,
Sum(U.CUR_MO_03_IS) AS SumOfCUR_MO_03_IS,
U.[Duplicate?]

FROM Unit_Revenue U RIGHT JOIN [Ref_Dashboard Base] D
ON (U.[DASHBOARD SO] = D.dashboard_rpt_order)
AND (U.DASHBOARD = D.dashboard_rpt)

WHERE (U.VIEW="2003ACT" Or U.VIEW Is Null)
AND (U.[Duplicate?] Is Null Or U.[Duplicate?]=No)

GROUP BY D.dashboard_rpt, D.dashboard_rpt_order, U.VIEW, U.[Duplicate?]

ORDER BY D.dashboard_rpt_order
[/tt][/blue]
A couple of points:

[li]Changed "HAVING" to "WHERE" since all the filters are field filters rather than filters on aggregate functions.[/li]
[li]I'm suspicious of U.[Duplicate?]=No. If the Duplicate field is a "Yes/No" field then you should be testing for TRUE or FALSE.[/li]