ACCESS FEED: Re: [MS_AccessPros] Issue with Report using Crosstab Query source

Senin, 05 Maret 2012

Re: [MS_AccessPros] Issue with Report using Crosstab Query source

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
>