I think John answered Bill Singer who had the same issue earlier today.
Find the Column Headings property of the crosstab query and enter all
possible MissedShift values into it.
Column Headings: 1,2,3 (or whatever)
Both John and I then suggested how to fill in with zeros where there is
no data.
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, "rodbevill"
<desertscroller@...> wrote:
>
> Here is the query, thanks for taking a look.
> Rod
>
> TRANSFORM Count([qryDriverAbsence-Graphic].[Date Affected]) AS
[CountOfDate Affected]
> SELECT [qryDriverAbsence-Graphic].DayOrder,
[qryDriverAbsence-Graphic].[Date Affected],
Count([qryDriverAbsence-Graphic].[Date Affected]) AS [Total Of Date
Affected], [qryDriverAbsence-Graphic].[Date Affected]
> FROM [qryDriverAbsence-Graphic]
> GROUP BY [qryDriverAbsence-Graphic].DayOrder,
[qryDriverAbsence-Graphic].[Date Affected],
[qryDriverAbsence-Graphic].[Date Affected]
> PIVOT [qryDriverAbsence-Graphic].MissedShift;
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" john@
wrote:
> >
> > Rod-
> >
> > Can you post the SQL for the Record Source of the report?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Nashua, NH)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
rodbevill
> > Sent: Monday, March 05, 2012 9:29 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Issue with Report using Crosstab Query
source
> >
> > I have designed a report to count and report employee absences based
on the work
> > shift for each day in a date range. The day of the week are the rows
while the
> > shifts are the columns. The issue is if there is no data for a
particular shift
> > the system shuts down. When looking at the design view of the
report, one
> > column shows "This control has an invalid control source." When the
data source
> > of the report is examined in datasheet view, the control does not
show since
> > there is no data associated with the work shift. Adding data then
everything
> > works fine. Is there a way use the report even if no data exist for
the control
> > source? Not sure if data will always exist for each work shift for
the date
> > range selected. Can a default value be associated with each control
source i.e.
> > 0 (zero)? The report show blanks except where data has been summed.
> >
> > Any ideas? Thanks in advance.
> > Rod
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
Tidak ada komentar:
Posting Komentar