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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. . |
#5
|
|||
|
|||
Pie chart troubles
So, the report is based on the Activity table?
Sorry a little confused by your response. |
#6
|
|||
|
|||
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. . |
#7
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|