Graph the data from multiple fields

[Sonny101]

Yup looked at the file, looks pretty neat. Was something done in the RowSource sql to make it something like this or was it some sort of vba code? Could that somehow be replicated to suit my graph, by changing certain values (cycle time, total entries to show etc), while retaining the code that 'rolls' the week/month?

[June7]

The VBA is to import data from spreadsheets and to open/close objects and to print report.

All data manipulation after import is accomplished with queries and calculations in queries and on form or report.

Certainly the concepts can be adapted for other time periods.

As for graphing the 'rolling' data, can't answer that. Might involve lots of VBA code and a 'temp' table. I have no interest to find out.

[Sonny101]

Oh ok then some way to show a general trend for a certain category, as to see whether total hours put into that category are increasing or decreasing. Maybe some sort of list that displays month in one column and the manhours for that category in other, and then overtime an entry gets added to that list (not necessarily removing the last one to only keep a certain amount of months, hence not really 'rolling'), then a button on the side which plots the last 6 or 12, or whatever amounts of time periods. Just need some way of showing the trend, nothing fancy.

[June7]

Trend - sounds like a line graph.

[Sonny101]

Ok I get it. Maybe lets leave out the graphing part but just some query that adds up the hours from the category every month, and displays that number in front of its respective date, hence it would be a long list of months with their hours. Something like

MONTH	HOURS (TROUBLESHOOTING)
July 2014	25
June 2014	28
May 2014	15
Apr 2014	12
Mar 2014	20
Feb 2014	15

PS How do you add a table to a post?

[June7]

SELECT Category, Format([datefield], "yyyymmm"), Sum([Hrs]) As SumHrs FROM query GROUP BY Category, Format([datefield], "yyyymmm");

Can copy/paste rows from an Access table into editor. It will be an image, not an editable table (you appear to have done this).

Click Go Advanced button below Quick Reply window for more editing tools, including table builder.

[Sonny101]

Yeah sorry about that I am a bit tired, I have am exam tomorrow, and also have to work on this project for office.
So wait this would be a report right? And pasted into RowSource sql? because what I did with the piecharts was, make a random piechart from the wizard, then paste the code for that in the RowSource sql. Where do I go about for this one?

[June7]

Didn't you ask for an SQL that would summarize hours by a date and category? I did not include a filter clause.

I feel this thread has gone on long enough. I think you have enough examples you can use as guidelines to generate variety of graphs. Just keep experimenting and getting more familiar with the SQL manipulation.

[Sonny101]

Yeah I know I promise this will be the last I don't understand where to write the code for something other than PieCharts, as like I said with piecharts it was simple enough that you can use query/table for data and then use a piechart/bar-graph and modify its RowSource sql to customize the graph as to your liking, but I dont know much about reports which display a table, with date as one column and Sum of Hours for a category as other. I tried using Reports>"Create a report in design view", And wizard to make a default Table, then modify its RowSource sql to write the code you told me to but could not find the sql there.

PS You can make a killer guide on graphing with all your posts on this thread, with various examples

PSS Or how about a graph which doesnt "roll" and Sums the hours on y-axis which on x-axis it categorizes the time in months for all the database. Maybe after 1 year someone could backup the year old data and delete it to make room for the data for next year, hence it would be a bit manual but would display the trend for that year

[June7]

I am not sure what you mean by 'display a table' and 'default Table'.

Reports have a RecordSource property. That's where SQL statement goes.

[Sonny101]

Sorry to bring this up again but if I type the following code I get hit by a bunch of popups asking me the value of 'datefield', 'ID', 'Tech', 'Date', 'Crew', 'Shift', 'Shfit', 'Category', 'Hrs' etc and in the end a blank table with columns on ID, Tech, Date and Crew:


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

Kinda feel bad for asking you again, but any idea what goes wrong here?

[June7]

Is datefield the actual name of the field?

Where are you inputting this statement - graph RowSource?

I don't know why the other popups are triggered - perhaps has something to do with the report RecordSource.

[Sonny101]

Nope inputting this in the RecordSource (see pic below), however if I change the

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

to

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

(Date is the actual field name), nothing much changes

[June7]

The report has textboxes bound to fields that are not included in the RecordSource. That will cause the popups.

Date is a reserved word. Should avoid using reserved words as names.

[Sonny101]

So even if I remove all those textboxes leaving behind only Category2, Date and Hrs (SELECT Category2, Format([Date], "yyyymmm"), Sum([Hrs]) As SumHrs FROM UnionT1 GROUP BY Category2, Format([Date], "yyyymmm"), only the 'Hrs' pops up, while the table that follows is still blank. As for the Date, I know now that it is a reserved word, but when I first started making the table I didn't, so now I am stuck with using 'Date'