Time Based Chart Query

[Babbage]

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!

 

[Justin]

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

 

[Babbage]

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

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!
.

 

[Guest]

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

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!
.

.

 

[Duane Hookom]

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.

 

[Babbage]

you lot are stars

many thanks

martin

 

[B]

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)?

 

[Pato-chan]

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.

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

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!
.

.