select query row empty if value zero/null

  • Thread starter Thread starter Zb Kornecki
  • Start date Start date
Z

Zb Kornecki

I have a query grouped on week of year converted to an integer. However if
the count value is 0 the entire row is omitted. I've tried the following to
force a value into the field w/o success. Please help. The entire SQL
statement for the query is also listed below. Thank-you Zb

Expr1: IIf(IsNull([weekly_Dashboard].[pt
accnt]),"",Count([weekly_dashboard].[pt accnt]))
Expr1: IIf(count([weekly_Dashboard].[pt
accnt])="","",Count([weekly_dashboard].[pt accnt]))
Expr1: IIf(count([weekly_Dashboard].[pt
accnt])=0,0,Count([weekly_dashboard].[pt accnt]))


SQL Statement:

SELECT
IIf(CInt(Format(weekly_dashboard.arrival,"ww",1,1))>52,1,CInt(Format(weekly_dashboard.arrival,"ww",1,1)))
AS Week, Count([Time intervals].[Pt Accnt]) AS [CountOfPt Accnt], Sum([Time
intervals].[Holding time]) AS [SumOfHolding time]
FROM Weekly_Dashboard INNER JOIN [Time intervals] ON Weekly_Dashboard.[Pt
Accnt] = [Time intervals].[Pt Accnt]
WHERE ((([Time intervals].[Holding time]) Is Not Null))
GROUP BY
IIf(CInt(Format(weekly_dashboard.arrival,"ww",1,1))>52,1,CInt(Format(weekly_dashboard.arrival,"ww",1,1)))
ORDER BY
IIf(CInt(Format(weekly_dashboard.arrival,"ww",1,1))>52,1,CInt(Format(weekly_dashboard.arrival,"ww",1,1)));
 
If Weekly_Dashboard.arrival has all weeks then use this change --

FROM Weekly_Dashboard LEFT JOIN [Time intervals] ON Weekly_Dashboard.[Pt
Accnt] = [Time intervals].[Pt Accnt]

If it does not then you need a table with a field that matches
Weekly_Dashboard.arrival with all the weeks and use this change --
--- UNTESTED -- UNTESTED ---
FROM (WeekTable LEFT JOIN Weekly_Dashboard ON WeekTable.Week =
Weekly_Dashboard.arrival) LEFT JOIN Weekly_Dashboard [Time intervals] ON
Weekly_Dashboard.[Pt Accnt] = [Time intervals].[Pt Accnt]
 
Back
Top