Graph the data from multiple fields

[June7]

Then the RowSource sql would be an aggregate query that groups on Technician Name and Sum each activity hours.

SELECT Table1.[Technician Name], Sum(Table1.[A1 L1]) AS [SumOfA1 L1], Sum(Table1.A1L2) AS SumOfA1L2, Sum(Table1.A1L3) AS SumOfA1L3
FROM Table1
WHERE (((Table1.[Technician Name])=[Forms]![Par-Pak final].[Technician Name]))


GROUP BY Table1.[Technician Name];

[Sonny101]

I type in this statement and get an error regarding punctuation:


SELECT Table1.[Technician Name], Sum(Table1.[A1 L1]) AS [SumOfA1 L1], Sum(Table1.A1L2) AS SumOfA1L2, Sum(Table1.A1L3) AS SumOfA1L3, Sum(Table1.A1L4) AS SumOfA1 L4, Sum(Table1.A1L5) AS SumOfA1L5, Sum(Table1.[A1 Anc]) AS [SumOfA1 Anc], Sum(Table1.A2L1) AS SumOfA2L1, Sum(Table1.A2L2) AS SumOfA2L2, Sum(Table1.A2L3) AS SumOfA2L3, Sum(Table1.A2L4) AS SumOfA2L4, Sum(Table1.A2L5) AS SumOfA2L5, Sum(Table1.[A2 Anc]) AS [SumOfA2 Anc], Sum(Table1.[A3 Inv]) AS [SumOfA3 Inv], Sum(Table1.[A3 Shop]) AS [SumOfA3 Shop], Sum(Table1.[A3 Facility]) AS [SumOfA3 Facility], Sum(Table1.[A3 Housekeeping]) AS [SumOfA3 Housekeeping], Sum(Table1.[A4 Installation]) AS [SumOfA4 Installation], Sum(Table1.[A4 Preparation]) AS [SumOfA4 Preparation], Sum(Table1.[A5 Discuss]) AS [SumOfA5 Discuss], Sum(Table1.[A5 Inspec]) AS [SumOfA5 Inspec], Sum(Table1.[A5 manual]) AS [SumOfA5 manual], Sum(Table1.[A5 Specify]) AS [SumOfA5 Specify]
FROM Table1
WHERE (((Table1.[Technician Name])=[Forms]![Par-Pak final].[Technician Name]))
GROUP BY Table1.[Technician Name];



Any ideas Thanks for the code though

[June7]

Why are you typing statement? Use the query builder. Click the ellipsis (...) to open.

[Sonny101]

Oh so thats what you meant, lol sorry my bad So when I select the fields from the box how do i select them as "sums"? . They just appear as normal fields not "SumOf...' something. And also what do you mean by WHERE and GROUP BY?

[June7]

Click Totals (with the Sigma symbol) on the Design ribbon.

WHERE clause is filter criteria. Enter parameters on the Criteria row.

The Total row will be available after clicking Totals. Select GROUP BY or SUM as appropriate.

Access Help has more guidelines on using the query builder for the various types of queries.

[Sonny101]

You mean like this?

Sorry these long office hours are killing me here Can't even get a single thing done

[Sonny101]

Oh nevermind got it. Had a typo in the code which was causing the error Thanks for all the help. One final thing, is it possible that when I view the graph in preview mode, the name of Technician shows up? Also when I print it there is a timestamp as to when this was printed?

[June7]

The db you provided has the technician name in the table. If this is not the actual data structure, then include the table with technician name in the query and join the tables on the common TechnicianID fields. This will make the name available for the query design grid.

Textbox on report can have expression in ControlSource property: = Now()

The filter criteria should be under only the TechnicianName field, not each of the hours fields.

[Sonny101]

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

The bar-graph worked perfectly, I spent the weekend on designing it and looks good But now for the pie-chart I was looking at this post and wanted to know that what you meant by "UNION must be typed or copy/paste in SQL View of query builder". Plus where am I to write. Plus what about this code which follows

"
SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L5", [A1L5] FROM Table1

"

[June7]

That is a UNION query sql statement. There is no wizard or designer for UNION. Open query builder and switch to SQL View. Type the SQL statement (or copy/paste the example) into the edit window.

However, if you the technician name is actually in another table, then each SELECT statement of the UNION can be an sql that joins the appropriate tables. Can use the query builder design grid to help you get the correct structure for the SELECT with joined tables. Then copy/paste the SELECT sql into the SQL View window and make edits as needed.

[Sonny101]

Ok I wrote the following statement in the sql view,



SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "A1L1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1L5", [A1L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A1 Anc", [A1 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2L1", [A2L1] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2L2", [A2L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2L3", [A2L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2L4", [A2L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2L5", [A2L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2 Anc", [A2 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Inventory", [A3 Inv] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Shop", [A3 Shop] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Facility", [A3 Facility] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Housekeeping", [A3 Housekeeping] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Installation", [A4 Installation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Preparation", [A4 Preparation] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Discussion", [A5 Discuss] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Inspection", [A5 Inspec] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Manual", [A5 manual] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Other(Specified)", [A5 Specify] FROM Table1;


Saved the querry as "UNION", went to reports>New>Chart Wizard, and selected the "UNION" query as to where the data comes from> Properties> RowSource and typed the following code

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;

But was greeted by this error

Any Ideas

[June7]

You named the query "UnionT1"?

[Sonny101]

Oh shoot my bad. Although this works , but is there any way to group the activities together, as the pie chart is cluttered with different categories? As in if I want to group all the A1 together(sum of all their individual activities such as A1L1 + A1L2 + A1L3 +A1L4 etc), and compare them with A2, A3? Also then create charts for A1, A2, A3, A4 repectively (Meaning for eg a chart containing only A1L1, A1L2, A1L3, A1L4, A1L5 A1 Anc)

[June7]

Create another field in the UNION, something like:

"A1" AS Category
...
"A2"
...
"A3"
...
"A4"
...
"A5"

Then the graph RowSource sql can be an aggregate query to include that field and group on it instead of Act field.

Create fields as needed for whatever level of aggregation you want.

[Sonny101]

So if I create one more field, how do I group 4 or more fields into that one(by summing them) Could you write the code for one of those groups so I can replicate them further to suit my needs. Also can you plz explain what you meant by RowSource sql being an aggregate query?