Time questions with partition

[Amin]

My first question is pretty straightforward. I want to get the difference in
minutes between two times. I am using CDATE(Time1 - Time 2) and I get the
following:

Time1 Time2 Difference
9:50:21 AM 9:11:04 AM 12:39:17 AM

This is what I want, but I need the difference to be in minutes, not in time.

My second question involves partitions. I am trying to count the number of
records in 15 minute intervals. For example

8:15
8:16
8:17
8:31
8:33

When I run my partition, I want my count to return three records between
8:15 and 8:30 and two records between 8:30 and 8:45

My code looks like this:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT TimeField, COUNT(*) AS [Total Workloads]
FROM [mytable]
Group BY TimeField
PIVOT Partition([TimeField],1,24,1);

This obviously does not work

 

[Stefan Hoffmann]

hi Amin,

Time1 Time2 Difference
9:50:21 AM 9:11:04 AM 12:39:17 AM
This is what I want, but I need the difference to be in minutes, not in time.

Int((Time1- Time2) * 1440)

My second question involves partitions. I am trying to count the number of
records in 15 minute intervals. For example

8:15
8:16
8:17

You need a partitioning function like:

Public Function GetIntervalNo(ATime As Date) As Integer
' There are 96 intervals (4 per hour, 0-95) per day:

GetIntervalNo = Int(TimeValue(ATime) * 96)

End Function



mfG
--> stefan <--

 

[John Spencer]

Use the DateDiff function to get the difference in units of time. Be aware
the DateDiff counts transitions. So 10:51:58 to 10:52:01 is 1 minute or 3
seconds.

DateDiff("n",Time2,Time1) will return the difference in miNutes (note M =
month so n = minutes, s = seconds)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Amin]

Thanks Stefan. The problem I'm having with DateDiff and Int is that it
produces negative numbers in the example I gave. Is there a way for it to go
around as in the example I gave?

Also, I created a module with the code you pasted as mdlInteger. How do I
put this in with my Partition code? Is this along the right lines?

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT TimeField, COUNT(*) AS [Total Workloads]
FROM [mytable]
Group BY TimeField
PIVOT Partition(mdlInteger(TimeField))

Thanks so much,
Amin

 

[Stefan Hoffmann]

Thanks Stefan. The problem I'm having with DateDiff and Int is that it
produces negative numbers in the example I gave. Is there a way for it to go
around as in the example I gave?

When you only have time values, then your given difference makes no
sense. At least for me. What does it mean, when Time1 > Time2?
Your difference is wrong, i must be ~23:39.

Also, I created a module with the code you pasted as mdlInteger. How do I
put this in with my Partition code? Is this along the right lines?

You renamed the function to mdlInteger()? This is bad name, as some
naming conventions use the prefix mdl* for modules.

Create a query like this:

SELECT
GetIntervalNo([TimeField]) AS IntervalNo
FROM [mytable]
GROUP BY GetIntervalNo([TimeField])

Save it and built your pivot query with it.


mfG
--> stefan <--

 

[Amin]

Sorry what I wrote was really unclear. Yes, what I would like to produce is
the following:


Time1 Time2 Difference
9:50:00 AM 9:11:00 AM 23:39:00

Or the difference could be in minutes or seconds, that obviously does not
matter.

Secondly, I renamed the module to GetIntervalNo. The module has this code in
it:

Public Function GetIntervalNo(ATime As Date) As Integer
' There are 96 intervals (4 per hour, 0-95) per day:

GetIntervalNo = Int(TimeValue(ATime) * 96)

End Function

I then called the function with this query:

SELECT GetIntervalNo([TimeField]) AS IntervalNo
FROM [WFLOW]
GROUP BY GetIntervalNo([TimeField])

But this will not run, and it prompts me with a dialog box that states:

"Undefined function 'GetIntervalNo' in expression."

Any ideas why this is happening?

Thanks,
Amin

Thanks Stefan. The problem I'm having with DateDiff and Int is that it
produces negative numbers in the example I gave. Is there a way for it to go
around as in the example I gave?

When you only have time values, then your given difference makes no
sense. At least for me. What does it mean, when Time1 > Time2?
Your difference is wrong, i must be ~23:39.

Also, I created a module with the code you pasted as mdlInteger. How do I
put this in with my Partition code? Is this along the right lines?

You renamed the function to mdlInteger()? This is bad name, as some
naming conventions use the prefix mdl* for modules.

Create a query like this:

SELECT
GetIntervalNo([TimeField]) AS IntervalNo
FROM [mytable]
GROUP BY GetIntervalNo([TimeField])

Save it and built your pivot query with it.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Amin,

Secondly, I renamed the module to GetIntervalNo. The module has this code in
it:
"Undefined function 'GetIntervalNo' in expression."
Any ideas why this is happening?

The name is ambigous. Rename the module to an other name than the
functions name.

The function itself must be placed in a standard module.


mfG
--> stefan <--

 

[Amin]

Thanks Stefan! I'm really close now with what I'm trying to get. My code is
the following:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT DateField
FROM [mytable]
GROUP BY Datefield, GetIntervalNo([TimeField])
PIVOT Partition(GetIntervalNo([TimeField]),0,96,1);


My problem is that this didn't really group by the Datefield appropriately.
The table that this produced looked like the following:

Date 25:25 26:26 27:27
28:28
10/01/08 1
10/01/08 4
10/01/08 5
10/02/08
8

I'm guessing this happened because of the way I used my Count. What I am
trying to do is count all of the records that happened during that time
interval. Thanks again for all your help!
Amin

 

[Stefan Hoffmann]

hi Amin,

Thanks Stefan! I'm really close now with what I'm trying to get. My code is
the following:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT DateField
FROM [mytable]
GROUP BY Datefield, GetIntervalNo([TimeField])
PIVOT Partition(GetIntervalNo([TimeField]),0,96,1);

The problem seems to be the additional Partion(). My function does the
partioning already. I think it must be dropped, try this:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate
DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT DateField
FROM [mytable]
GROUP BY Datefield, GetIntervalNo([TimeField])
PIVOT GetIntervalNo([TimeField]);


mfG
--> stefan <--

 

[Amin]

Thanks! Works perfectly!

hi Amin,

Thanks Stefan! I'm really close now with what I'm trying to get. My code is
the following:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT DateField
FROM [mytable]
GROUP BY Datefield, GetIntervalNo([TimeField])
PIVOT Partition(GetIntervalNo([TimeField]),0,96,1);

The problem seems to be the additional Partion(). My function does the
partioning already. I think it must be dropped, try this:

PARAMETERS Forms!Switchie!StartDate DateTime, Forms!Switchie!EndDate
DateTime;
TRANSFORM COUNT(*) AS Occurences
SELECT DateField
FROM [mytable]
GROUP BY Datefield, GetIntervalNo([TimeField])
PIVOT GetIntervalNo([TimeField]);


mfG
--> stefan <--