OK. The following depicts a working/tested example which
I would have zipped and uploaded if this forum had that
capability.
(1) Create tblTest5 with 3 fields--Process (text),
StartTime (date/time), EndTime (date/time).
Populate it as shown in your first example, e.g.
1234ABC 1/1/2008 12:20 AM 1/2/2008 10:30 PM
WXYZ9999 1/1/2008 1:30 AM 1/3/2008 11:30 AM
(2) Create table Nums with 1 field--Num (number)
Populate it with 10 records containing 0 to 9,
respectively.
(3) Create the following three queries (copy the
SQL as shown), naming them as shown.
(4) Run the queries.
*****************************************************
Query96: Returns the records from tblTest5,
broken into 48 1/2 hour time slots
per day.
Note: 0.020833332 represents a 1/2 hour
portion of a day as stored by Access.
See:
http://support.microsoft.com/kb/q130514/
SELECT
tblTest5.Process
, tblTest5.EndTime
, DateValue([starttime])+[x]*0.020833332 AS Expr1
, Int(TimeValue([expr1])/0.020833332)+1 AS TimeBlock
FROM
Numbers2
, tblTest5
WHERE
(((DateValue([starttime])+[x]*0.020833332)<[EndTime]))
ORDER BY
tblTest5.Process
, DateValue([starttime])+[x]*0.020833332;
*****************************************************
Query97: Cross-tab query that uses the Partition()
function to provide a visual display of the
TimeBlocks for each process
TRANSFORM Count(Query96.TimeBlock) AS CountofTimeBlock
SELECT
Query96.Process
, DateValue([query96].[Expr1]) AS StartDate
, DateValue([query96].[endtime]) AS EndDate
FROM
Query96
GROUP BY
Query96.Process
, DateValue([query96].[Expr1])
, DateValue([query96].[endtime])PIVOT Partition([TimeBlock],1,48,1);
*****************************************************
Query97: Make-table query that creates tblTotals and
populates it with the sum of processes, by
day.
SELECT
Query97.StartDate
, Sum(Query97.[1: 1]) AS [SumOf1: 1]
, Sum(Query97.[2: 2]) AS [SumOf2: 2]
, Sum(Query97.[3: 3]) AS [SumOf3: 3]
, Sum(Query97.[4: 4]) AS [SumOf4: 4]
, Sum(Query97.[5: 5]) AS [SumOf5: 5]
, Sum(Query97.[6: 6]) AS [SumOf6: 6]
, Sum(Query97.[7: 7]) AS [SumOf7: 7]
, Sum(Query97.[8: 8]) AS [SumOf8: 8]
, Sum(Query97.[9: 9]) AS [SumOf9: 9]
, Sum(Query97.[10:10]) AS [SumOf10:10]
, Sum(Query97.[11:11]) AS [SumOf11:11]
, Sum(Query97.[12:12]) AS [SumOf12:12]
, Sum(Query97.[13:13]) AS [SumOf13:13]
, Sum(Query97.[14:14]) AS [SumOf14:14]
, Sum(Query97.[15:15]) AS [SumOf15:15]
, Sum(Query97.[16:16]) AS [SumOf16:16]
, Sum(Query97.[17:17]) AS [SumOf17:17]
, Sum(Query97.[18:18]) AS [SumOf18:18]
, Sum(Query97.[19:19]) AS [SumOf19:19]
, Sum(Query97.[20:20]) AS [SumOf20:20]
, Sum(Query97.[21:21]) AS [SumOf21:21]
, Sum(Query97.[22:22]) AS [SumOf22:22]
, Sum(Query97.[23:23]) AS [SumOf23:23]
, Sum(Query97.[24:24]) AS [SumOf24:24]
, Sum(Query97.[25:25]) AS [SumOf25:25]
, Sum(Query97.[26:26]) AS [SumOf26:26]
, Sum(Query97.[27:27]) AS [SumOf27:27]
, Sum(Query97.[28:28]) AS [SumOf28:28]
, Sum(Query97.[29:29]) AS [SumOf29:29]
, Sum(Query97.[30:30]) AS [SumOf30:30]
, Sum(Query97.[31:31]) AS [SumOf31:31]
, Sum(Query97.[32:32]) AS [SumOf32:32]
, Sum(Query97.[33:33]) AS [SumOf33:33]
, Sum(Query97.[34:34]) AS [SumOf34:34]
, Sum(Query97.[35:35]) AS [SumOf35:35]
, Sum(Query97.[36:36]) AS [SumOf36:36]
, Sum(Query97.[37:37]) AS [SumOf37:37]
, Sum(Query97.[38:38]) AS [SumOf38:38]
, Sum(Query97.[39:39]) AS [SumOf39:39]
, Sum(Query97.[40:40]) AS [SumOf40:40]
, Sum(Query97.[41:41]) AS [SumOf41:41]
, Sum(Query97.[42:42]) AS [SumOf42:42]
, Sum(Query97.[43:43]) AS [SumOf43:43]
, Sum(Query97.[44:44]) AS [SumOf44:44]
, Sum(Query97.[45:45]) AS [SumOf45:45]
, Sum(Query97.[46:46]) AS [SumOf46:46]
, Sum(Query97.[47:47]) AS [SumOf47:47]
, Sum(Query97.[48:48]) AS [SumOf48:48] INTO tblTotals
FROM
Query97
GROUP BY
Query97.StartDate;
*****************************************************
HTH - Bob
[quoted text clipped - 29 lines]
