Graph the data from multiple fields

[Sonny101]

Hello everyone. I have made a form in access 2003, and wanted to add a graphing tool to it. I tried using the wizard in "Reports" tab, but all that does is makes a graph of how many times a certain number of hour was worked. What I wanted was to make a graph/pie-chart for all the 'Hour' entries in the form for a particular technician, which displays the technician's distribution of time for each activity. I have googled also but nothing showed up for this particular scenario. Thanks


PS the form: https://app.box.com/s/fdkcc3jiblaaa9dwpmvr

[azhar2006]

Watch this Thread

http://www.databasejournal.com/featu...7-Database.htm

[Sonny101]

Yes azhar I have tried that but firstly it does not let me select all the fields in my form, and even if i select a few of those fields, it will display the data for all the technicians, rather than just one technician over the years.

[June7]

Filter the graph RowSource sql for a single technician.

[Sonny101]

Filter the graph RowSource sql for a single technician.

A little explanation on how to do that step by step would be appreciated . And what do I do to select all the fields for the graph?

[June7]

I don't know your data structure and so can't know that 'selecting all the fields' will produce what you want.

Click the ellipsis (...) to open the RowSource query builder. Include whatever fields you want. Add filter criteria. Can be reference to a control on report or form that has technician ID.

You should be aware that graphs work better on reports than forms.

[Sonny101]

Sorry if this is dumb but how do i get to properties? I have tried creating graph through reports>new>chart wizard, but that opens up a page with a strange chart in the end.

[June7]

Yes, the wizard usually doesn't give you the desired final product. Use the wizard to establish chart but then always have to make changes for chart type and its RowSource.

Get to Properties Sheet for chart the same way as any other control on form or report. The RowSource is on the Data tab of Properties Sheet.

Double click (or right click) on chart to open up other chart configuration options.

[Sonny101]

Ok i did as you asked and in the query builder added all the fields that I want on the pie chart, but the chart does not display all those fileds, rather than just the generic East West North. I have added the screenshots. And how do I add a filter criteria to reference to control on form so that it sticks to that name?

[June7]

Add the TechnicianID field to the query grid and set filter: [Reports]![report name].[control name]

What happens when you switch to Report View? Save the report while in Report View (or Layout View), close then reopen in Design View. The graph should then show your stuff.

I've never built a pie chart. I am not sure your query will work. I analyse graphing issues best when I can work with data. If you still need help, provide the db. Follow instructions at bottom of my post.

[Sonny101]

How do I set filter like that? The chart did kinda change when I switch between views but had a uncomprehendable way of displaying information . I have attached the file with a records for 5 days. Thanks

[Sonny101]

Sorry that was the older version, here is the newer file.

PS One more thing, the graph displays the bar for every name, which would become useless, as the database is to expand exponentially over time with new names and all, and a bar for every name will quite impossible. So basically what I wanted was a graph for every name, which shows their distribution of hours in each activity, over the time.

[June7]

A field for each activity is not normalized data structure. Cannot get the graph you want with this table structure. Should be like:

Tech	Act	Hrs	Date	Shift	Crew
1	A	4	7/15/2014	1	1
1	B	5	7/15/2014	1	1
1	C	3	7/15/2014	1	1
1	D	6	7/16/2014	1	2
1	E	2	7/17/2014	1	1
2	A	4	7/15/2014	1	1
2	B	7	7/16/2014	1	3
2	C	3	7/16/2014	2	1
2	D	2	7/17/2014	1	1

It is possible to manipulate the data to a normalized structure with UNION query then use the UNION as source for graphing. UNION must be typed or copy/paste in SQL View of query builder. For some reason, I cannot type UNION in the example query. Replace ______ with UNION.
SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
______ SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L2", [A1L2] FROM Table1
______ SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L3", [A1L3] FROM Table1;

Pie graph RowSource:
SELECT UnionT1.Act, Sum(UnionT1.Hrs) AS SumOfHrs FROM UnionT1 WHERE (((UnionT1.Tech)=[Forms]![Par-Pak final].[Technician Name])) GROUP BY UnionT1.Tech, UnionT1.Act;

Correction. If you want bar chart then your table can work. (A normalized structure would actually require a CROSSTAB query for bar graph. Applying filter criteria in CROSSTAB is different from other queries.) The graph RowSource would be like:
SELECT [Date], [A1 L1], A1L2, A1L3 FROM Table1 WHERE ((([Technician Name])=[Forms]![Par-Pak final].[Technician Name]));

Date is a reserved word. Should not use reserved words as field names. Also, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Note the space in [A1 L1].

[Sonny101]

Yeah I realized that piechart was giving me problems, while bar-graph atleast tried to display something useful. So(for the bar-graph) I type SELECT [Date], [A1 L1], A1L2, A1L3 FROM Table1 WHERE ((([Technician Name])=[Forms]![Par-Pak final].[Technician Name])); in the RowSource, after typing all the fields in the code? Which would be:

SELECT [Date], [A1 L1], A1L2, A1L3, A1L4, A1L5, [A1 Anc], A2L1, A2L2, A2L3, A2L4, A2L5, [A2 Anc], [A3 Inv], [A3 Shop], [A3 Facility], [A3 Housekeeping], [A4 Installation], [A4 Preparation], [A5 Discuss], [A5 Inspec], [A5 manual], [A5 Specify] FROM Table1 WHERE ((([Technician Name])=[Forms]![Par-Pak final].[Technician Name]));

[Sonny101]

Finally I typed the above code, and the graph is finally closer to something I was thinking . But the issue is, it is displaying a separate bar for each date, which although in this case looks okay-ish as there are only 5 days to look at, but as the database grows up, the chart would be horribly mashed up. What I wanted was rather than a separate bar for each date, a single bar for the sum of dates.

So on the y-axis we would have the hours, x-axis would display activities, while single bars for the sum of that particular activity, covering all ​the dates that are in the database.