Graph the data from multiple fields

[Sonny101]

No no what I meant was a piechart not restricted to one technician only, but the total hours put into that activity by the workforce. So it shouldn't be categorized by the technician names but the sum of all the manhours put in by all the technicians for a particular category.

[Sonny101]

So a exact same pie-chart but unlike the one attached (which is of a particular Technician) but for the total manhours put in the category by the workforce.

[June7]

Then change the RowSource sql as appropriate for the grouping you want.

SELECT Category, Sum(Hrs) FROM table GROUP BY Category;

[Sonny101]

yup got it

SELECT Category, Sum(Hrs) FROM UnionT1 GROUP BY Category;

Can I make one more category in the query, so then I can categorize the fields differently, without messing up with the original category? So what I mean to say is that I want to make another general Pie-Chart for A1 (A1L1, A1L2, A1L3 etc), not specified to any technician, but a total distribution of manhours.

[June7]

Don't really understand the question.

Add fields to the UNION as you see fit.

Make as many pie charts as you want, each with their own RowSource.

[Sonny101]

umm something along the lines of:

SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "Break Down Troubleshooting" AS Category, "Line 1" AS Category2, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 2", "A1L2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 3", "A1L3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 4", "A1L4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 5", "A1L5", [A1L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Ancillary", "A1 Anc", [A1 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L1", [A2L1] FROM Table1


Then Row sql to be:

SELECT Category2, Sum(Hrs) FROM UnionT1 GROUP BY Category2;

[June7]

Yes, you got it. Test it. Showing what you want?

[Sonny101]

Doesnt work Got an error saying "The number of columns in the two selected tables or queries of a union do not match"


Full code:

SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "Break Down Troubleshooting" AS Category, "Line 1" AS Category2, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 2", "A1L2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 3", "A1L3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 4", "A1L4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 5", "A1L5", [A1L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Ancillary", "A1 Anc", [A1 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L1", [A2L1] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L2", [A2L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L3", [A2L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L4", [A2L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2L5", [A2L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", "A2 Anc", [A2 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", "Inventory", [A3 Inv] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", "Shop", [A3 Shop] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", "Facility", [A3 Facility] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", "Housekeeping", [A3 Housekeeping] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Assigned Project", "Installation", [A4 Installation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Assigned Project", "Preparation", [A4 Preparation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", "Discussion", [A5 Discuss] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", "Inspection", [A5 Inspec] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", "Manual", [A5 manual] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Other Activity", "Other(Specified)", [A5 Specify] FROM Table1;

[June7]

Did not continue the "Line 1", "Line 2", etc for Category2 on all rows.

[Sonny101]

I only want the first six entries for the graph

[June7]

Doesn't matter, each row must have same number of fields. Put some value or use Null.

ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L1", [A2L1] FROM Table1

[Sonny101]

Ok I wrote this code:

SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "Break Down Troubleshooting" AS Category, "Line 1" AS Category2, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 2", "A1L2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 3", "A1L3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 4", "A1L4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Line 5", "A1L5", [A1L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Break Down Troubleshooting", "Ancillary", "A1 Anc", [A1 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L1", [A2L1] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L2", [A2L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L3", [A2L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L4", [A2L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2L5", [A2L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preventative Maintenance", Null, "A2 Anc", [A2 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", Null, "Inventory", [A3 Inv] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", Null, "Shop", [A3 Shop] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", Null, "Facility", [A3 Facility] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Maintenance /Improv", Null, "Housekeeping", [A3 Housekeeping] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Assigned Project", Null, "Installation", [A4 Installation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Assigned Project", Null, "Preparation", [A4 Preparation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", Null, "Discussion", [A5 Discuss] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", Null, "Inspection", [A5 Inspec] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Self-Improvement Activity", Null, "Manual", [A5 manual] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Other Activity", Null, "Other(Specified)", [A5 Specify] FROM Table1;


With this row sql:

SELECT Category2, Sum(Hrs) FROM UnionT1 GROUP BY Category2;

But got a piechart with all the six categories that I want, PLUS a huge "slice 1". I think that is because of that Null, and it sums all the "Null" categories. Any ideas?

[June7]

SELECT Category2, Sum(Hrs) FROM UnionT1 WHERE Not Category2 Is Null GROUP BY Category2;

[Sonny101]

Oh nice! That works Is there anyway to make a overall bar-graph of a certain category (A1L1, A1L2, A1L3,... A1 Anc) with total hours in the y-axis, and the x-axis displaying the last 12 months, so the bar-graph displays the total number of hours worked in each month, but (here's the tricky part) the months should cycle and be restricted to last 12 months only. So lets say it is Aug now, and for this month the x-axis should go from this Aug to last years Sep, and when Sep comes, the x-axis should go from this Sep to last years Oct (discarding the last month as time progresses). I know its kinda confusing but it would be great if you could help me out with it.


Something like this (for for 12 months)

[June7]

Rolling time period is complicated. I have one db that produces a report that 'rolls' the days/weeks for a 2 week period (time and attendance). Never thought about graphing.

Here is file.

Ooops! Just found an error on the report for calculating days of the second week. See if you can find it.