time intervals

[Ed Weitz]

I am using Access 97.

I have a table with a list of dates and times when an
order was created. I would like to run one query that
would produce a count of orders entered in half hour
increments starting at 00:00:00AM to 23:59:59 PM.

The results would then be exporeted to an Excel
spreadsheet to generate a chart of the results.

My immediate fix was to create a series of queries, that
calculated the total for each half hour increment and
manually enter the results in Excel.

 

[Dale Fye]

Without knowing your data structure, this is just a stab in the dark.

This extracts the datepart of the OrderDate, then uses the DateAdd,
DateDiff, and DateValue functions to extract the number of minutes
after midnight an order was made, determine which half hour period it
was in, then add it back to midnight, so that all orders placed during
a particular half hour period will be credited as though they occurred
at the beginning of the period. It then counts the number of orders
by each day, and half hour period.

SELECT DateValue(OrderDate) as OrderDt,
DateAdd('n', INT(DateDiff('m', DateValue(OrderDate),
OrderDate)/30) * 30, DateValue(OrderDate)) as StartIncrement,
Count(*) as OrderCount
FROM yourTable
GROUP BY DateValue(OrderDate),
DateAdd('n', INT(DateDiff('m', DateValue(OrderDate),
OrderDate)/30) * 30, DateValue(OrderDate))
--
Hope this gives you some ideas about how to accomplish your goals.

Dale Fye


I am using Access 97.

I have a table with a list of dates and times when an
order was created. I would like to run one query that
would produce a count of orders entered in half hour
increments starting at 00:00:00AM to 23:59:59 PM.

The results would then be exporeted to an Excel
spreadsheet to generate a chart of the results.

My immediate fix was to create a series of queries, that
calculated the total for each half hour increment and
manually enter the results in Excel.

 

[Van T. Dinh]

If you don't care about the actual date and only want to group by
half-hourly intervals, try:

SELECT Count(O.OrderID) AS CountOfOrders,
Int(48*(TimeValue(O.OrderDateTime))) AS IntervalNo
FROM tblOrder AS O
GROUP BY Int(48*(TimeValue(O.OrderDateTime)));


If you want to group by date AND half-hourly intervals, try:

SELECT Count(O.OrderID) AS CountOfOrders,
DateValue(O.OrderDateTime) AS WholeDate,
Int(48*(TimeValue(O.OrderDateTime))) AS IntervalNo
FROM tblOrder AS O
GROUP BY DateValue(O.OrderDateTime),
Int(48*(TimeValue(O.OrderDateTime)));