If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Based Chart Query
Hi there everyone
Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! |
#2
|
|||
|
|||
You need to use the date Format or DatePart to combine the
data. The row source for your graph should be something like. SELECT Format([When],'hh') AS Expr1, Sum([Value]) as Expr2 GROUP BY Format(Now(),'hh')... or use Count([Value]) for text data -----Original Message----- Hi there everyone Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! . |
#3
|
|||
|
|||
its not the data values that are a problem, thats no problems,
or getting a nice 10am, 11am, 12pm etc. on the xaxis its the variable positioning of the data between those values im having trouble with (as shown in my example url) - xaxis values that arent regularly spaced and dont match the step size of the x axis martin "Justin" wrote: You need to use the date Format or DatePart to combine the data. The row source for your graph should be something like. SELECT Format([When],'hh') AS Expr1, Sum([Value]) as Expr2 GROUP BY Format(Now(),'hh')... or use Count([Value]) for text data -----Original Message----- Hi there everyone Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! . |
#4
|
|||
|
|||
Your graph is simple graphing the data with the axis
formated by hour. The query statement does more than format the graph axis. It groups your data by the hour (Format([when],'hh') and sums the data for that hour (Sum ([Value])). This will give you what you want. Addition work will be needed to get missing hours (ie if there is no data between 11:00 and 11:59 then ther will be no hour 11 in your results). -----Original Message----- its not the data values that are a problem, thats no problems, or getting a nice 10am, 11am, 12pm etc. on the xaxis its the variable positioning of the data between those values im having trouble with (as shown in my example url) - xaxis values that arent regularly spaced and dont match the step size of the x axis martin "Justin" wrote: You need to use the date Format or DatePart to combine the data. The row source for your graph should be something like. SELECT Format([When],'hh') AS Expr1, Sum([Value]) as Expr2 GROUP BY Format(Now(),'hh')... or use Count([Value]) for text data -----Original Message----- Hi there everyone Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! . . |
#5
|
|||
|
|||
If you really want to have some fun with this consider not using a graph.
1) create a report based on your data with -detail section 4" high -report footer 4" high -report width of 6.5" 2) add your two text boxes to the detail section and name them txtTime and txtNum -txtNum should be fairly narrow 3) add code to the On Format Event of the detail section Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.txtNum.Top = Me.Detail.Height - _ (Me.Detail.Height * Me.txtNum / 20) Me.MoveLayout = False Me.txtNum.Left = (Me.txtTime - #10:00:00 AM#) * _ (Me.Width * 1 / #4:00:00 PM#) End Sub 4) run the report 5) consider adding lines/rectangle to the report footer for your "graph grid" If your earliest and latest times will be much earlier or later then you will need to adjust some values in the code. For samples of this type of report, check the Corp Tech Demos at http://www.invisibleinc.com/divFiles.cfm?divDivID=4. -- Duane Hookom MS Access MVP -- "Babbage" wrote in message ... Hi there everyone Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! |
#6
|
|||
|
|||
you lot are stars
many thanks martin |
#7
|
|||
|
|||
Can this be done without coding? Is there a way to graph mixed times series
information (i.e. monthly data for 6 months that continues in biweekly increments)? "Babbage" wrote: you lot are stars many thanks martin |
#8
|
|||
|
|||
Time Based Chart Query
What would that additional work include if I need to get missing hours to
show up on a graph? That's exactly what I need. :-) " wrote: Your graph is simple graphing the data with the axis formated by hour. The query statement does more than format the graph axis. It groups your data by the hour (Format([when],'hh') and sums the data for that hour (Sum ([Value])). This will give you what you want. Addition work will be needed to get missing hours (ie if there is no data between 11:00 and 11:59 then ther will be no hour 11 in your results). -----Original Message----- its not the data values that are a problem, thats no problems, or getting a nice 10am, 11am, 12pm etc. on the xaxis its the variable positioning of the data between those values im having trouble with (as shown in my example url) - xaxis values that arent regularly spaced and dont match the step size of the x axis martin "Justin" wrote: You need to use the date Format or DatePart to combine the data. The row source for your graph should be something like. SELECT Format([When],'hh') AS Expr1, Sum([Value]) as Expr2 GROUP BY Format(Now(),'hh')... or use Count([Value]) for text data -----Original Message----- Hi there everyone Im trying to produce a graph based on the following set of data: when value 10:00:00 17 12:47:32 10 13:14:15 12 13:17:29 14 15:28:00 6 16:06:09 19 18:35:37 7 I want a graph that goes from like 10am to 7pm in an even hour by hour bases, (or the last value, doesnt matter too much) and I want the values in the graph to get placed at relevant time points along the time (x) axis. eg. hand drawn picture of what im after http://www.jitsu.net/misc/graph.jpg but all I can do is get them regularly spaced along the x axis any ideas? many thanks martin! . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Master Form based on Query - Help! | Ben Johnson | Running & Setting Up Queries | 1 | August 6th, 2004 02:06 PM |
Filtering records in a form based on data in a subform or query. | jbuck | Using Forms | 0 | August 5th, 2004 02:51 PM |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |
Delete Query based on result of select query | John Finch | Running & Setting Up Queries | 1 | July 2nd, 2004 05:47 PM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |