Time Chart

[ncbroncos]

Is there a way to create a time chart where I can have a courier enter their times into a form and those times fall into the correct spot on a chart??



For instance
-- courier has two stops == 2:13 pm and 2:33 pm

time slot courier times
2:00 - 2:15 2:13
2:16 - 2:30 empty
2:31 - 2:45 2:33
2:46 - 3:00
etc...

Thanks

[Clayton]

I’d try the following:

1. A table into which to put courier times, [CourierTable], with [CourierTime] of type “Date/Time”

A brief explanation of Microsoft date values: AFAIK, Access handles dates similar to Excel, in that a date value, such as 07/25/2013 12:22:54 PM, has a decimal value determined as follows:
DecimalValue = Number of Days since Reference Date + fraction of the day so far
In Access2010, the reference date for me is 12/30/1899 12:00:00 AM
So, 07/25/2013 12:22:54 PM = 41480 + 12/24 + 22/60/24 + 54/60/60/24 = 41480.515903

1. A query to sort times into the chunks you want. I’ve heard this referred to as “Binning” before. Something like:

SELECT [CourierTime], <build formula>
FROM [CourierTable]

Where <build fomula> is:
Int([CourierTime])+Int(([CourierTime]-Int([CourierTime]))/(15/60/24))*(15/60/24)

This will drop your time back to the nearest fifteen minutes (that’s the 15 in “15/60/24”)

There are many variants you could do using this approach.
You could have a table, [TimeSpansOfFifteenMinutes], that looks like:
[TimeSpanID] [StartTime] [EndTime]
1 00:00 00:30
2 00:15 00:30
3 00:30 00:45
4 00:45 01:00
5 01:00 01:15
…
154 14:30 14:45
155 14:45 15:00
156 15:00 15:15
157 15:15 15:30
…
188 23:00 23:15
189 23:15 23:30
190 23:30 23:45
191 23:45 24:00

And you could generate the value of TimeSpanID as follows:
([CourierTime]-Int([CourierTime]) / (15/60/24)
([CourierTime]-Int([CourierTime]) gives you the time of day sans date effect, and /(15/60/24) gives you which 15 minute span your’e looking at.

I’d have 2 queries to generate the dadtasource for your form:
Query 1:
SELECT [CourierTime], ([CourierTime]-Int([CourierTime]) / (15/60/24) AS CourierBinID
FROM [CourierTable];

Query2:
SELECT [StartTime], [EndTime], [CourierTime]
FROM [CourierTable] JOIN [TimeSpansOfFifteenMinutes] ON [CourierTable].[ CourierBinID] = [TimeSpansOfFifteenMinutes]. [TimeSpanID]

Then, on your form, have a button that says “ENTER VALUE” than, when clicked, actually causes a requery of your form. Display Query2 on your form, and maybe have a filter so that only records with a courier time falling BETWEEN Int(Now()) AND Int(Now())+1 are ever shown.

Does that help?

[ncbroncos]

I will start working on this. I see the theory in the table but until I work on it I just cant see it. Allot of great info I will be using. Thanks