Time based graph

[skizzly]

Hello all,

I have the following query:

TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number
SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
FROM [Copy of qryDailyCountOfValidations]
GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
PIVOT Format([Start Time],"Short Time");

it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day

What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....

the y axis is the count of employees
the x axis is the start time of their shift


10
9
8
7 xxxxxxxx
6 x x
5 x x
4 xxxxxxxx x
3 xxxxxxx x
2 xxxxxxxxxx
1
0
00:00 03:15 05:30.........................23:59

So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...

the level of detail needed is to plot for every 15 minutes.
The [end time] is not strictly needed, just included to show what columns i have available

I would appreciate any advice in achieving this on a graph on an access 2010 report...

i have updated a test database so you can see what the data looks like

thanks in advance

 

[JHB]

And what exactly is your problem doing that?

 

[skizzly]

Hi JHB,

thanks for quick reply, it is the processing of getting the values to the chart as described.... particularly the 15 minute interval

I can get the values to the graph but the scales are in correct.

I need the graph to show the individual day and within that, the 24 hours that make that day up, in hh:mm format, the number of employees on shift that start at particular hours in that day's 24 hour... thanks for your patience

 

[JHB]

Try using the In clause, (not tested):

PIVOT Format([Start Time],"Short Time") IN (00:00,00:15,00:30, ....) ;

 

[skizzly]

Hi JHB,

thanks for that tip.... that works for column headers, how do i then group those hours into the day so it shows:

01/05/2015
00:00, 00:15.....23:59

thanks...

 

[JHB]

Does it not do that?
Could you show what you get and what you want, (print screen)?

 

[skizzly]

Hi JHB,

thank you for keeping with me...

please see attached zip with some more detail

 

[JHB]

Oh - yet I think know what you are after, but sorry you can't do that only using queries, you need some code.
Try the attachment, open the only form click the button and look at the table "TheTimeTable"!

 

[skizzly]

Hello JHB,

Brilliant pointers! Really Appreciate the help. Getting there.

Now createing the chart for fromTheTimesTable table and the chart wizard only allows 6 columns... what is the best way to incorporate the TheTimesTable into a line graph?

Thanks

 

[JHB]

It should show them all not only 6, I'll look at it later today or tomorrow!
What is the Rowsource for the graph, check it and post.

 

[skizzly]

Hi JHB,

Attached zip is how far i have got to.

The Rowsource is set to "Query1", but no data is pulled through....

I have added a couple of buttons to go for ward and back by one day...

but the report fails and does not bring up what i expected.

The chart is a scatter xy. i have made an error somewhere and any advice on this would be greatly appreciated.

 

[JHB]

A report is (as I see it) a hardcopy of your data, (piece of paper), and not the right container/ frame when you want to scroll forward and backward in your data, then use a form instead it is perfect for that and you've much more control to manipulate different things in a form.
If you want to print out certain dates, then send it direct to a printer from the form via a report, use a button for that.
I've made a solution for you in the attached database, using a form, look at it and come back if you've any question. It is not a scatter xy, but a normal column graph, which you easy can change to another graph type.
Remark - I'm using the "Link Master Fields" and "Link Child Fields" to trigger the graph to update.
Only for clarifying, the rowsource in your report, isn't "Query1" as you wrote, then you've change the rowsource by code.

 

[skizzly]

Hi JHB,

Thanks for the tips. Will redesign... however when i look at the new report no data is pulled through ....

As for the rowsource, i was trying to find the best way to enable user to move forward and back in time... you have helped address that..

 

[skizzly]

Hi JHB,

findured it out... needed to have .requery in the on the _AfterUpdate() event

Thanks for your time.. much appreciated

 

[JHB]

You're welcome, good luck.