Prev: Modifying legend on graph on report
Next: Format duplicate date in report
From: Opal on 20 May 2010 12:07
I've inherited a DB in Access 2003 and I am trying create a
query that will create a pie chart. One table lists [RegularHours
Worked] and [OTHoursWorked] by [InputDate]. There is a
Separate field called [Activity] which is related to an "Activity"
table where a [Category] field exists.

I need a pie chart by [Category] to show the # of hours by
category as compared to the total hours - but I need
to combine the [RegularHoursWorked] and [OTHoursWorked].

So, my data is something like this:

InputDate RegHrs OTHours Category
Apr 1 10 4 1 Safety
Apr 2 10 2 0 Quality

And my query results for the "Safety" Category to create
my pie chart should be:

Safety hours Total hours
5 7

Can anyone point me in the right direction to get
the desired results?

thank you
From: Duane Hookom on 20 May 2010 14:16
Do you expect multiple charts or are you concerned only with the Safety hours
compared with all other hours?
A Row Source of the following might work to create a pie chart with two
slices:

SELECT IIf(Category ="Safety","Safety","Other") as Safe ,
Sum(RegHrs +OTHours) As Hrs
FROM [One Table]
GROUP BY IIf(Category ="Safety","Safety","Other");



--
Duane Hookom
Microsoft Access MVP


"Opal" wrote:

> I've inherited a DB in Access 2003 and I am trying create a
> query that will create a pie chart. One table lists [RegularHours
> Worked] and [OTHoursWorked] by [InputDate]. There is a
> Separate field called [Activity] which is related to an "Activity"
> table where a [Category] field exists.
>
> I need a pie chart by [Category] to show the # of hours by
> category as compared to the total hours - but I need
> to combine the [RegularHoursWorked] and [OTHoursWorked].
>
> So, my data is something like this:
>
> InputDate RegHrs OTHours Category
> Apr 1 10 4 1 Safety
> Apr 2 10 2 0 Quality
>
> And my query results for the "Safety" Category to create
> my pie chart should be:
>
> Safety hours Total hours
> 5 7
>
> Can anyone point me in the right direction to get
> the desired results?
>
> thank you
> .
>
From: Opal on 20 May 2010 15:22
I am building multiple charts....

Safety hours against all others, then Quality Hours against all
others.... etc.... 6 pie charts in all.
From: Opal on 20 May 2010 15:47
Hmmm....

I get:

Safety Hrs
Other 160
Other 461
Other 662.5
..
..
..

I think the problem is the set up of the two tables and should be more
clear as the set up is poor (I hate inheriting databases from people
who don't know how to properly create a relational database :-S)

The activity table has 3 fields:

Indicator: Is a text field containing "Safety", "Quality", "Cost",
etc
(6 indicators in all)
ActivityID: is the auto number field but no primary key assigned
Activity: Is a text field and there are 95 different activities noted
but each classified as one of the 6 indicators above.

The hours tracking table has several fields but the ones of note are:

ID: Autonumber field with primary key assigned
InputDate: Date hours worked
RegHours: number field for regular hours worked
OTHours: number field for overtime hours worked
Activity: text field with activites drawn from the 95 above

I am wondering if it would be prudent to properly set up these
relationships to get the queries to work properly... but there are
over
3000 records in the hour tracking table and established reporting
that this could possibly affect.
From: Duane Hookom on 20 May 2010 16:12
Then, you should be able to create a report based on your unique category
values. Add a chart with a Row Source like:

SELECT Category, RegHrs+OTHours As CatHrs,
(SELECT Sum(RegHrs+OTHours) FROM [One Table] ot
WHERE ot.Category <> [One Table].Category) As RemainderHrs
FROM [One Table]
GROUP BY Category;

Set the Link Master/Child properties to the Category field.

--
Duane Hookom
Microsoft Access MVP


"Opal" wrote:

> I am building multiple charts....
>
> Safety hours against all others, then Quality Hours against all
> others.... etc.... 6 pie charts in all.
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Modifying legend on graph on report
Next: Format duplicate date in report