Rod-
Ah. Include an IN clause that lists all the Shifts you want. In Query Design,
open the Properties window, click the background of the upper part of the query
to get the query properties, then enter all the shifts in the Column Headings
property, separated with semi-colons. (If the shift names are text, enter them
surrounded with quotes.) That will guarantee that you get all shifts in your
query, even if there is no data.
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 10:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar