sorting info in a table

[Creslin]

I am not sure where to ask this so I will put it here. I have a table with
three fields of data and the time that each field was put into the database.
I was wondering if there is a way to sort the fields be half hour increments,
to show how many times each field was used in a half hour and do it for a
twelve hour period. Is it even possible and if so how would I go about doing
it, please. Thank you.

 

[Jerry Whittle]

You don't do this in a table.

You could do it in a query based on the table.

Better yet create a report based on a query that limits the table data to
the last 12 hours. You can use the report's sorting and grouping options to
group the time field in half-hour increments and do things like Count() of
the field.

 

[Ken Sheridan]

You can return a value corresponding to each half of each hour in the formats
yyyy-mm-dd hh 0 and yyyy-mm-dd-hh 1 with the following expression:

Format([YourDateTimeColumn],"yyyy-mm-dd hh ") &
IIf(DatePart("n",[YourDateTimeColumn])< 30,0,1)

Group a query on this expression and count the rows per group. To do it for
a 12 hour period, e.g. 8.00 AM to 7.59 PM today restrict the query in the
WHERE clause:

SELECT FORMAT([YourDateTimeColumn]," yyyy-mm-dd hh ") &
IIF(DATEPART("n",[YourDateTimeColumn])< 30,0,1) AS HalfHourSlot,
COUNT(*) AS TimesUsed
FROM [YourTable]
WHERE [YourDateTimeColumn] >= #2008-08-05 08:00:00#
AND [YourDateTimeColumn] < #2008-08-05 20:00:00#
GROUP BY FORMAT([YourDateTimeColumn]," yyyy-mm-dd hh ") &
IIF(DATEPART("n",[YourDateTimeColumn])< 30,0,1);

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I am not sure where to ask this so I will put it here. I have a table with
three fields of data and the time that each field was put into the database.
I was wondering if there is a way to sort the fields be half hour increments,
to show how many times each field was used in a half hour and do it for a
twelve hour period. Is it even possible and if so how would I go about doing
it, please. Thank you.

I'm not sure what you mean here. "Sort" in Access has a very specific meaning:
to put records into a defined sequential order. The phrase "sort the fields be
half hour increments" is evidently using the word in a different sense.

You're also - I think - assuming that Access somehow records when a field was
"used". Unless you're recording this information yourself, you're out of luck
- Access does not track that information.

At a GUESS, you have a date/time field in the table and want to somehow do
subtotals or counts at half hour intervals based on this count. If so you can
use a Totals query using a calculated field:

Timeslot: CDate(Fix(Cdbl([timefield]) * 48) / 48)

You can Group By this field and Count the other fields in a Totals query.