A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pie chart troubles



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 05:07 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

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
  #2  
Old May 20th, 2010, 07:16 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Pie chart troubles

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
.

  #3  
Old May 20th, 2010, 08:22 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

I am building multiple charts....

Safety hours against all others, then Quality Hours against all
others.... etc.... 6 pie charts in all.
  #4  
Old May 20th, 2010, 08:47 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

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 a

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.
  #5  
Old May 20th, 2010, 09:12 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Pie chart troubles

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.
.

  #6  
Old May 20th, 2010, 09:25 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

So, the report is based on the Activity table?

Sorry a little confused by your response.

  #7  
Old May 20th, 2010, 10:57 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Pie chart troubles

I am maybe more confused than you since I can't see your data or possibly
understand what you want.

Your earlier post with "My data looks like this" didn't have an Activity
field. Can you please explain your data, some table/query/field names, and
desired display?

--
Duane Hookom
Microsoft Access MVP


"Opal" wrote:

So, the report is based on the Activity table?

Sorry a little confused by your response.

.

  #8  
Old May 21st, 2010, 07:52 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

Okay, I hope that this helps you understand:

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.

Indicator ActivityID Activity
Safety 267 Audit Process
Safety 193 Pedestrian separation
Quality 104 Service Fixture
.....

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

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

ID InputDate RegHours OTHours Activity
1 05/01/10 2 0 Service
Fixture
2 05/02/10 0.5 0.5 Pedestrian
Separation
3 05/03/10 1 0 Service
Fixture

So the what I would want the query to return based on the above is:

Indicator Hours
Safety 1.0

Total Hours 4.0


and a pie chart with 1.0 slice for Safety out of the total 4.0 hours
worked.

Is that a little clearer?

  #9  
Old May 22nd, 2010, 05:53 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Pie chart troubles

My answer remains basically the same. Create a query [qselTracking] that
joins the Activity table and the tracking table so you have the fields
Indicator and TotHrs (RegHours+OTHours). Then create this query based on the
qselTracking to be used as the row source of the chart control:

SELECT Indicator, TotHrs As IndicatorHrs,
(SELECT Sum(TotHrs) FROM [qselTracking] ot
WHERE ot.Indicator [qselTracking].Indicator) As RemainderHrs
FROM qselTracking
GROUP BY Indicator;

Create report based on Select Distinct Indicator from the activity table.
Use the link master/child on the Indicator field.

--
Duane Hookom
MS Access MVP

"Opal" wrote in message
...
Okay, I hope that this helps you understand:

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.

Indicator ActivityID Activity
Safety 267 Audit Process
Safety 193 Pedestrian separation
Quality 104 Service Fixture
....

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

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

ID InputDate RegHours OTHours Activity
1 05/01/10 2 0 Service
Fixture
2 05/02/10 0.5 0.5 Pedestrian
Separation
3 05/03/10 1 0 Service
Fixture

So the what I would want the query to return based on the above is:

Indicator Hours
Safety 1.0

Total Hours 4.0


and a pie chart with 1.0 slice for Safety out of the total 4.0 hours
worked.

Is that a little clearer?

  #10  
Old May 26th, 2010, 07:35 PM posted to microsoft.public.access.reports
Opal[_3_]
external usenet poster
 
Posts: 133
Default Pie chart troubles

Duane, thanks for your support....

I just went back through this and found the
cause of all my troubles.... the Hours worked
fields are "text" fields, not number fields....!?!?

I just went back to the requestor to ask why...

I hate inheriting databases

I have put this on hold until the requestor
decides why this is so and what he wants to
do.....there are other reports and charts based
on this data and I don't want to make any further
changes without their clearance first.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.