Graph the data from multiple fields

[June7]

The UNION query just creates the field that can be used as a grouping criteria.

Use the UNION query as source for another query that does the aggregate calcs.

See post24 where you quote my earlier post. I show an aggregate query as the pie graph RowSource.

SELECT Category, Sum(Hrs) AS SumHrsCat FROM UnionT1 WHERE (((UnionT1.Tech)=[Forms]![Par-Pak final].[Technician Name])) GROUP BY Category;

[Sonny101]

Oh so I should create a new query with only the fields that I want (which would make that query a group), and make a pie-chart based on that? Such as like this:

(sql querry named Union2T1)
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;

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


And so on?
I still dont understand how I would a able to sum a particular group and compare them


PS The above RowSource sql ask me for the Technician's Name twice

[June7]

My suggestion is to build one UNION query and then use it like a table - as a source for other queries. Do not have to build another UNION query. Build one with all the data you will need. Then use that query to build other queries.

Your last post does not show any adjustment to the UNION for the new Category field.

I really don't know how I can better describe what to do.

[Sonny101]

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.

No what I meant to say was what would be the syntax for the field which would group several fields. Would it be something like this?

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
SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "A1" AS Category, FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A2" AS Category, FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "A3" AS Category, FROM Table1;

and so on? Sorry if this is a dumb question as I am new to all of this, and am still learning to do all this

I did however previously created another Union query, which was able to do some of the stuff I had in mind, but can you figure out why the following RowSource sql asks me for the Technician name twice?

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

[June7]

No, just create the new field in the lines already provided as example:

SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "A1" AS Category, "A1L1" AS Act, [A1 L1] AS Hrs FROM Table1
______ SELECT ID, [Technician Name], [Date], Crew, Shift, "A1", "A1L2", [A1L2] FROM Table1
...

Again, use that dataset as source for other queries, including aggregate query to group by Category.


The RowSource sql is for the graph? Is the form bound to dataset that has [Technician Name] field and/or is there a combobox of that name?

[Sonny101]

Oh so you mean this?


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



And the RowSource then being:

SELECT Category, Sum(Hrs) AS SumHrsCat FROM UnionT1 WHERE (((UnionT1.Tech)=[Forms]![Par-Pak final].[Technician Name])) GROUP BY Category;



To answer your second question, the form has a field [Technician Name]

[June7]

Yes. However, as shown in my examples, it is not necessary to repeat field name. The first line establishes field names. There is a space missing for the first A4 line anyway - "A4"AS

I would have to review your latest graph attempts to analyze issue. Is graph on form or report?

[Sonny101]

something may have happened when I copy pasted here, that space is there in the original code. The code however does not work, as it asks for all the parameters from A1 to A5 when I try to graph it. The graph is on the report, I will attach the latest file of database here. Also I created various UNION querys as was in a hurry to show someone what I had in mind, instead of learning to aggregate at that time.

[June7]

The UNION query does not have the Category field.

Your NewMouseHook procedure prevents me from opening form. I get error message 'must be updated for use on 64-bit systems'. I deleted the code.

The form's RecordSource sql is pulling all the fields twice because of Table1.* wildcard and explicitly referencing fields. Do one or the other in SQL or just reference the Table1 because not applying any filter or sort parameters. Then all the controls don't need the Table1 qualifier in ControlSource. Report works.

[Sonny101]

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





Oh so I have simplified the code. What do you mean by the "Category field"? It's already there in the above code for the UNION query. Is it supposed to be somewhere else also?

[June7]

The Category field wasn't in any of the queries in the posted database. Did you provide wrong version of db?

[Sonny101]

Oh yeah maybe. Here is the current one.

[June7]

Access doesn't like the quote characters around the A1, A2, A3, A4, A5, A6 values. Look real close and you will see they are slightly different (angled) from the other quote marks (and now that I know to look for I can see them in the post as well). Replace them.

I have known this to happen when copy/paste text from forums or other sources.

[Sonny101]

Oh shoot, didn't notice that Good catch Oh and what if I wanted the same pie-chart with the same categories, but not restricted to one technician, but the total of all the hours technician have worked on. As in for category "A1", total of A1L1, A2L2 etc summed for all the technicians. Plus the same thing (General PieChart) but just for A1 fields, but with all hours worked there by all the technicians.

[June7]

A pie chart showing each category for each technician will probably be indecipherable.

For pie chart for one Category showing all technicians, try:

SELECT Technician, Sum(Hrs) AS SumHrs FROM union1 WHERE Category="A1" GROUP BY Technician;