Time difference Query

[glenmac]

I have a table that accepts orders and when they are placed the current time is also inserted using the time() function, what I need to do is group the orders by time. For instance if the orders are placed at 12 01 and 12 02 and 12 03 I need to group them together and count the group of orders as 1. I was trying to use;
select TimeOrdered from orders where
Where TimeOrdered between Time() and Time() + 5
groupby TimeOrdered;
but it doesn't work.
Can anyone help me with the syntax to;
1 return the TimeOrdered between an alotted time as a single entity.
2 Count the grouped orders
All help will be more than appreciated.

 

[frankiem]

For 10 minute groupings, you could use:

select Left([TimeOrdered],4) from orders
groupby Left([TimeOrdered],4);

 

[glenmac]

Not quite what I need but thanks for your interest. I think my answer lies with datediff but I can't figure how to get the date diff between the same field but in different records.

 

[RezaNana]

acctually, you should link your access with a VB program,i have already tried it for my time card programmes and it works.

 

[mpastore]

Can you provide a few sample records and a sample of what you would want the resulting query to look like?

Mike Pastore

Hats off to (Roy) Harper

 

[glenmac]

it the table I have records that are like this
Shipping_ID ShipMethod Driver_ID ShipComments ShipDate ShipTime
Shipping_ID is aan autonumber. ShipMethod is either delivery or Pickup,ShipComments are directions the driver can input. And shipdate and time are self explanitory.
A typical record contains;
1,delivery,9,turn left off freeway,02/07/2003,9:52:56 PM
1,delivery,9,turn left off freeway,02/07/2003,9:53:03 PM
1,delivery,9,turn left off freeway,02/07/2003,9:53:10 PM
As these are each orders that are part of the same delivery I would like to group them together by their shiptimes and count it as 1 delivery. The code so gratiously provided by
frankiem is close but messes up when times change to double diget hours, IE 10,11,12.
Psuedo Query;
Select Count(Shiptime)as Complete_Order from Shipping
Where datediff(&quot;n&quot;,Shipping.ShipTime,format(now,&quot;hh:mm:ss&quot)<=2
Groupby Shiptime
Hope this explains more clearly what I'm attempting to do. Thanks for your help.

 

[mpastore]

I see your dilemma. You are trying to infer one unique delivery by the proximity of times to each other. You can get close by joing the table to itself and datediff'ing each time to another, however not sure how to establish bounderies between one start time to another end time.

Why not just use the shipping ID to group the records?

Mike Pastore

Hats off to (Roy) Harper

 

[glenmac]

Interesting thought. Hmm, how would I do that?