Graph the data from multiple fields

[June7]

There is no field in the query called Hrs - it is SumHrs. Bind textbox to SumHrs.



Could change the field name. All direct references in other queries will reflect the change, however, usage in expressions probably won't.

I use Rick Fisher's Find and Replace for Access tool when I want to do global design changes like that. Costs about $50. Saved my sanity more than once.

[Sonny101]

Oh right. Well now it asks me for a 'date' parameter and comes up with this. It doesnt display the month/year column (hence the popup), sums up in a funny way (Somehow still linking it all up with 'Line 1', 'Line 2' etc and such), and sum of hours for category2 (which is basically A1 L1, A1L2, A1L3 etc) for that particular month.


PS Yeah a tool like that would be pretty helpful I guess

[June7]

You have the Date field in an expression to apply format but don't give it an alias name. Just like Hrs is not a field in the query, neither is Date.

SELECT Category2, Format([Date], "yyyymmm") AS YrMo, Sum([Hrs]) As SumHrs FROM UnionT1 GROUP BY Category2, Format([Date], "yyyymmm");

No idea why it is not aggregating the Category2 values.

[Sonny101]

Somewhat better now. Oh I guess it is because in the original Union file, the first five entries are a single category (Category2), so when it runs the code, it groups them up and displays them individually rather grouping and summing the entries. Have a look at this:

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, "Break Down Troubleshooting", "Other", "Other", [A1O] 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, "Preventative Maintenance", Null, "Other", [A2O] 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, "Maintenance /Improv", Null, "Other", [A3O] 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, "Assigned Project", Null, "Other", [A4O] 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, "Self-Improvement Activity", Null, "Other", [A5O] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Other Activity", Null, "Other(Specified)", [A5 Specify] FROM Table1;

[June7]

The query is grouping by Category2 and YrMo. There should be only one record in the report for each combination of those values.

[Sonny101]

Exactly it shouldn't show multiple entries for every combination. How about I change the recordsource to read from the Table instead of query? Something like:

SELECT Sum(Table1.[A1 L1]) AS [A1L1], Sum(Table1.A1L2) AS A1L2, Sum(Table1.A1L3) AS A1L3, Sum(Table1.A1L4) AS A1L4, Sum(Table1.A1L5) AS A1L5, Sum(Table1.[A1 Anc]) AS [A1 Anc], Sum(A1O) AS [A1O]
FROM Table1
GROUP BY Format([Date], "yyyymmm");

Or change the existing recordsource to

SELECT Category2, Format([Date], "yyyymmm") AS YrMo, Sum([Hrs]) As SumHrs FROM UnionT1 GROUP BY Format([Date], "yyyymmm"); (Not to group by Category but by date)


Or if it is simpler a bar graph that displays the Sum of hours for the first six fields (shouldnt be a category, as that particular category was only limited to UnionT1) on the y-axis, while MM/YY on the x-axis (with each bar for each month)

SELECT Sum(Table1.[A1 L1], Table1.A1L2, Table1.A1L3, Table1.A1L4, Table1.A1L5, Table1.[A1 Anc], Table1.A1O) AS SumHrs,
FROM Table1
GROUP BY Format([Date], "yyyymmm"), SumHrs;


PS A1O is an entry that I later added to the table

[June7]

The UNION query should work as source but, yes, if you don't need data from the other tables of the UNION, then try the one table as source for query.

Did it work?

[Sonny101]

Nope tried the rowsource for the bargraph but didnt work Gave an error that there might be some punctuation mistakes or something of that sort. I suspect this is because of some syntax error in that RowSource sql.

I used this for the bar graph:

SELECT Sum(Table1.[A1 L1], Table1.A1L2, Table1.A1L3, Table1.A1L4, Table1.A1L5, Table1.[A1 Anc], Table1.A1O) AS SumHrs,
FROM Table1
GROUP BY Format([Date], "yyyymmm"), SumHrs;

Can we use Sum() expression and use multiple fields inside that operator to sum them all up together like I have done above?

[June7]

Can't use aggregate function to add multiple fields. Aggregate function aggregates records of a single field.

SELECT Sum([A1 L1]), Sum(A1L2), Sum(A1L3), Sum(A1L4), Sum(A1L5), Sum([A1 Anc]), Sum(A1O)
FROM Table1
GROUP BY Format([Date], "yyyymmm");

or Sum the added fields:

SELECT Sum([A1 L1] + A1L2 + A1L3 + A1L4 + A1L5 + [A1 Anc] + A1O) AS SumHrs
FROM Table1
GROUP BY Format([Date], "yyyymmm");

Arithmetic with null results in null. Handle possible null with Nz: Nz([A1 L1],0)

[Sonny101]

Another quick Q: For the bar graph to display MMYY and total hours, it shouldn't GROUP BY anything as all of the hours in the table is a is a single record. So in that case how do I aggregate the hours for each month and display them in a bar-graph with SumHrs/MMYY ?

[June7]

Your question is contradictory. If you want data by MMYY then need to GROUP BY MMYY. What does 'all of the hours in the table is a single record' mean - how can the table have a single record?

[Sonny101]

Yeah I was kind of confused because all the above sql produces is four borders with no graph and no errors


PS And If I draw the graph using the good old wizard (using UnionT1) with category2 on y-axis and date on x-axis it makes a graph, but its count is off by about a 100 hours . Following is the sql from the wizard

SELECT (Format([Date],"MMM 'YY")),Count([Category2]) AS [CountOfCategory2] FROM [UnionT1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY"));

[June7]

Why do you have (Year([Date])*12 + Month([Date])-1) in the GROUP BY clause?

Why would you multiple a year (say 2014) by 12 and then add month (say 8)?

[Sonny101]

I didnt do any of it, the wizard did it. I just dragged the date to the x-axis and the count of category2 to y-axis. The graph that came out somehow looked closer to what I wanted so I said hey why not. But the thing is the count if off by around 100 hours. Maybe it is counting the null category also I dont know, but do you why that is happening? (I'll also add hours for later months to see how it works out)

PS The sql is rom when the graph was made by the wizard i just went to RowSource and copied the code from there to here

[June7]

I don't understand how wizard can construct that expression.