Graph the data from multiple fields

[Sonny101]

Me neither (but there were two separate boxes for date and date by month), but I tried working out on these ROwSource's, they do the same thing but different ways but none of them work. Can you figure out why and what can be done to fix them?

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




SELECT Sum([A1 L1] + A1L2 + A1L3 + A1L4 + A1L5 + [A1 Anc] + A1O) AS SumHrs
FROM Table1
GROUP BY Format([Date], "yyyymmm"); (Shows no error but no graph shows up either)



SELECT Sum([A1 L1]), Sum(A1L2), Sum(A1L3), Sum(A1L4), Sum(A1L5), Sum([A1 Anc]), Sum(A1O)
FROM Table1
GROUP BY Format([Date], "yyyymmm");
(Something totally different shows up, pic below)

[June7]

I did not assign alias field names so Access did it for me: Expr1001, Expr1002, etc.

I also did not show the grouping field in the SELECT clause.

As stated, simple arithmetic with Null results in Null. If any of the terms of addition are null the result will be Null. Null does not graph.

Adjust queries as necessary.

Other than removing the weird expression, I don't know how to fix the wizard generated query. I have no idea why it would be off by 100 hours.

[Sonny101]

For anyone who later stumbles on this thread later I just found a work around for this problem(pic of graph below). May not be the best solution, but it works. Just create a new query with just the first five (or any number) or fields that are to be added together. I've had something liek this:

SELECT ID, [Technician Name] AS Tech, [Date], Crew, Shift, "Line 1" As Act, [A1 L1] AS Hrs FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Line 2", [A1L2] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Line 3", [A1L3] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Line 4", [A1L4] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Line 5", [A1L5] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Ancillary", [A1 Anc] FROM Table1
UNION SELECT ID, [Technician Name], [Date], Crew, Shift, "Other", [A1O] FROM Table1;


Note: 'AS Act' part can be neglected though

Go to reports>New>Chart Wizard while selecting your new query>Bar Graph> Drag Sum of Hrs box to y-axis, Date by month box to x-axis, drag the box from the main chart out so to leave it blank and Presto you are done!

The RowSource source sql that the graph made is: SELECT (Format([Date],"MMM 'YY")),Sum([Hrs]) AS [SumOfHrs] FROM [Union6T1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY"));

Somehow the Count operator I was using previously couldn't do the maths right!

Dont ask me about '(Year([Date])*12 + Month([Date])-1)' part it just is a way to display month and year i guess.

One thing I dont understand is when I am designing the graph, if I stretch it beyond a certain point it cuts itself off even if there is plenty of space(pic below) For a graph like this I want it to be as wide as possible. Workaround for this would be appreciated.

[June7]

Did you resize the graph control or did you double click into the graph control and resize the graph frame?

[Sonny101]

I just double clicked into the graph and dragged the frame lines that appear. It stretches fine for some time(making the graph wider) but after a certain point it starts to cut the graph if i keep on dragging.

[June7]

Yep, what I suspected. Have to size both the graph control and the graph frame, and even then the results can be wonky. Lots of trial and error. And there will always be quite a bit of white space around the graph frame.

[Sonny101]

How do you size the graph control?

[June7]

The same as any control. Select it, click an anchor to grab and drag.

[Sonny101]

Thanks for all your support, those graphs work and look perfectly how I had in my mind (couldn't have been possible without your help), even while stretched to fit longer sheets of paper. I hate to bring this up again, but while accessing graphs, is it possible to view graphs for a certain time-period? As in when we open those graphs, a box pops up asking from when to when should we want the data to be graphed, and after entering the data it makes the graph for the time-period specified?

[June7]

I don't remember how we produced the RowSource sql for each of the graphs. If the final sql does not have a date field then somewhere in the query sequence there must be a date field that can be used to filter records. The parameter inputs can be popup prompts or reference to controls on form (I never use popups in query).

[Sonny101]

Doesnt matter what the parameter(date) inputs are as long as it works. How do we filter the records twice, first by Technician name and then by the time period specified ('From(date)' and 'To')? Nope the sqls we used didnt have the date field :/

For example

Individual- Activities (As in filtered by technician name, grouped with a category)
SELECT Category, Sum(Hrs) AS SumHrsCat FROM UnionT1 WHERE (((UnionT1.Tech)=[Forms]![Par-Pak final].[Technician Name])) GROUP BY Category;

Total- Activities (Not filtered by technician name, just general)
SELECT Category, Sum(Hrs) FROM UnionT1 GROUP BY Category;

Total-Break Down troubleshooting (General with a category)
SELECT Category2, Sum(Hrs) FROM UnionT1 WHERE Not Category2 Is Null GROUP BY Category2;

Would be helpful if you could show me with these so I can replicate with the rest

[June7]

Is there a date field in UnionT1? Build criteria referencing controls on form same as done for technician.

SELECT Category, Sum(Hrs) FROM UnionT1 WHERE [datefield] BETWEEN [Forms]![Par-Pak final].[startdate] AND [Forms]![Par-Pak final].[enddate] AND Tech=[Forms]![Par-Pak final].[Technician Name] GROUP BY Category;

Or if the values are in textboxes on the report: [datefield] BETWEEN [tbxStartDate] AND [tbxEndDate] AND Tech = [tbxTech]

[Sonny101]

Yup! Works beautifully Though for the pop-ups the message they display is [Forms]![Par-Pak final].[startdate] and[Forms]![Par-Pak final].[enddate]. Anyway to change that to 'Start Date' and 'End Date'? Plus in the actual graph, how do I make textboxes displaying the start date and the end date for each respectively, so the person can also see afterwards what time period he is looking at.

[June7]

If you have form open there should not be any popups.

AFAIK, displaying date range in the graph would require VBA code to change graph title.

Suggest you just use textboxes on report to display the date range. Expression in textbox would reference the form controls.

Can change the parameters in graph RowSource to reference the report textboxes: [tbxStart] AND [tbxEnd]

[Sonny101]

I meant pop-up as in pop-ups to enter the date(pic below), and yeah I just wanted to use textboxes to display date range, don't know what to put as the control source for the text box though