How to calculate time interval

[Astrom33]

Hello everyone. I am as new to this forum as I am to Microsoft Access.

I am working on a case at the law firm I work at. Basically the table I have shows all point of sale (cash register) transactions for a particular person. It has the following columns: Store Number; Date; Time, Ticket Number, Amount $; Subtotal; Tax; and Employee ID.

What I want to do is be able to show that this particular employee was always making sales and therefore was never away from the register more than 30 minutes.

For example: the first transaction shows that on, say, 01/06/2001 at 10:01 am a sale was made. The second transaction shows that another sale was made on 01/06/2001 at 10:16 a.m. The third shows another sale at 10:22 a.m. The table goes on forever.

What I need to do is be able to find a way to create a query (I think) wherein a column will show the time between the first transaction and the second transaction; the time between the second transaction and the third....and so on.

I can do it manually by just seeing that obviously between the 1st and 2nd sale the interval was 15 minutes and the time between the 2nd and 3rd sale was 6 minutes. However, the table is quite lengthy and I would like to know if Access could help me out in any way.

I would GREATLY appreciate any help.

[ADezii]

Hello everyone. I am as new to this forum as I am to Microsoft Access.

I am working on a case at the law firm I work at. Basically the table I have shows all point of sale (cash register) transactions for a particular person. It has the following columns: Store Number; Date; Time, Ticket Number, Amount $; Subtotal; Tax; and Employee ID.

What I want to do is be able to show that this particular employee was always making sales and therefore was never away from the register more than 30 minutes.

For example: the first transaction shows that on, say, 01/06/2001 at 10:01 am a sale was made. The second transaction shows that another sale was made on 01/06/2001 at 10:16 a.m. The third shows another sale at 10:22 a.m. The table goes on forever.

What I need to do is be able to find a way to create a query (I think) wherein a column will show the time between the first transaction and the second transaction; the time between the second transaction and the third....and so on.

I can do it manually by just seeing that obviously between the 1st and 2nd sale the interval was 15 minutes and the time between the 2nd and 3rd sale was 6 minutes. However, the table is quite lengthy and I would like to know if Access could help me out in any way.

I would GREATLY appreciate any help.

I think this would be difficult to accomplish via a Query, but I'll let some of the more SQL Proficient Moderators/Experts have a look and see what they come up with. I can probably arrive at a solution via the code route, but again I'll see if someone comes up with a better solution first. Right now, I'm going on Vacation but I'll check back in a couple of days.

[Astrom33]

Thanks for that reply. Too bad you're going on vacation. Anyone else have any ideas?

[Astrom33]

*******bump**** *****

[StevoNZ]

The DateDiff function will calculate the time interval for you, but you need to identify the next transaction in sequence. As this is not is the same row, I think maybe a pivot table would be more helpful???

[Scott Price]

Not sure about PivotTables since I hate the things... Here's a code function (place it in a code module) That you can call to populate a text box/label etc with the data you are looking for: The code assumes you have a form named frmTransTime, with a listbox named lstTransTime. The listbox draws it's information from a query that returns three columns: TransDate, TransTime and TransID in that order! It won't work if your columns are in a different order :-)

I made an unbound textbox on the frmTransTime, then in the on open event I added Call TransDiff and Me!txtTransTime = TransDiff lines of code.

It resulted in this output:

Between Transaction: 1 And Transaction: 2 are 6 minutes. Between 2 And 3 are 3 minutes. Between 3 And 4 are 6 minutes. Between 4 And 5 are 1 minutes. Between 5 And 6 are 6 minutes. Between 6 And 7 are 4 minutes. Between 7 And 8 are 3 minutes.

[CODE=vb]
Public Function TransDiff() As String

Dim MyArray() As Variant
Dim TransTime As Date
Dim TransTime1 As Date
Dim TimeDiff As Long
Dim TransID As Integer
Dim Temp As String
Dim Temp1 As String
Dim Temp2 As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim ls1 As Integer
Dim intCounter As Integer
Dim list As Integer
Dim list1 As Integer

Set rs = Forms!frmTransT ime!lstTransTim e.Recordset
ls = Forms!frmTransT ime!lstTransTim e.ListCount
With rs
.MoveFirst
MyArray() = .GetRows(ls)
End With

TransTime = MyArray(1, 0)
TransTime1 = MyArray(1, 1)
TransID = MyArray(2, 0)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = "Between Transaction: " & Temp1 & " And Transaction: " & Temp1 + 1 & " are " & Temp & " minutes. "

list = 0
list1 = 1
ls1 = ls - 2

For intCounter = 1 To (ls1)
If list1 <= (ls1) Then
list = list + 1
list1 = list1 + 1
TransTime = MyArray(1, list)
TransTime1 = MyArray(1, list1)
TransID = MyArray(2, list)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = Temp2 & "Between " & Temp1 & " And " & Temp1 + 1 & " are " & Temp & " minutes. "
End If

Next
TransDiff = Temp2
End Function
[/CODE]

ADezii's code would probably be simpler and have error handling and all those goodies in it, just cos he's that kind of guy :-) But since he's on vacation and you're in a hurry!

Hope this helps.

Regards,
Scott

[Astrom33]

Thanks for that reply. I am fairly new at this, but I will try and make it work.

[Astrom33]

Not sure about PivotTables since I hate the things... Here's a code function (place it in a code module) That you can call to populate a text box/label etc with the data you are looking for: The code assumes you have a form named frmTransTime, with a listbox named lstTransTime. The listbox draws it's information from a query that returns three columns: TransDate, TransTime and TransID in that order! It won't work if your columns are in a different order :-)

I made an unbound textbox on the frmTransTime, then in the on open event I added Call TransDiff and Me!txtTransTime = TransDiff lines of code.

It resulted in this output:
[CODE=vb]
Public Function TransDiff() As String

Dim MyArray() As Variant
Dim TransTime As Date
Dim TransTime1 As Date
Dim TimeDiff As Long
Dim TransID As Integer
Dim Temp As String
Dim Temp1 As String
Dim Temp2 As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim ls1 As Integer
Dim intCounter As Integer
Dim list As Integer
Dim list1 As Integer

Set rs = Forms!frmTransT ime!lstTransTim e.Recordset
ls = Forms!frmTransT ime!lstTransTim e.ListCount
With rs
.MoveFirst
MyArray() = .GetRows(ls)
End With

TransTime = MyArray(1, 0)
TransTime1 = MyArray(1, 1)
TransID = MyArray(2, 0)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = "Between Transaction: " & Temp1 & " And Transaction: " & Temp1 + 1 & " are " & Temp & " minutes. "

list = 0
list1 = 1
ls1 = ls - 2

For intCounter = 1 To (ls1)
If list1 <= (ls1) Then
list = list + 1
list1 = list1 + 1
TransTime = MyArray(1, list)
TransTime1 = MyArray(1, list1)
TransID = MyArray(2, list)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = Temp2 & "Between " & Temp1 & " And " & Temp1 + 1 & " are " & Temp & " minutes. "
End If

Next
TransDiff = Temp2
End Function
[/CODE]

ADezii's code would probably be simpler and have error handling and all those goodies in it, just cos he's that kind of guy :-) But since he's on vacation and you're in a hurry!

Hope this helps.

Regards,
Scott

I tried, but I couldn't get it to work. I am sure its because I'm making a mistake. I think I am making a mistake as to where exactly to plug in these codes. When I am in the form frmTransTime I clicked on the "code" button at the top it brought up the form's code as follows:


[U]Option Compare Database
--------------------------------------------------------------------------------

Private Sub Detail_Click()

End Sub
--------------------------------------------------------------------------------
Private Sub Form_Load()

End Sub
--------------------------------------------------------------------------------
Private Sub Form_Open(Cance l As Integer)
Call TransDiff and Me!txtTransTime = TransDiff lines of code
End Sub
----------------------------------------------------------------------------------
Private Sub List0_BeforeUpd ate(Cancel As Integer)

End Sub
----------------------------------------------------------------------------------
Private Sub Text2_BeforeUpd ate(Cancel As Integer)

End Sub
---------------------------------------------------------------------------------




I plugged in the "Call TransDiff and Me!txtTransTime = TransDiff lines of code" below where it says Private sub form_Open (Cancel As Interger), but it gives me an error box that says Compile Error: Expected: end of statement.


As to the Code (vb) I do not know where to plug that in. As you can tell I am completely new to this side of access and really do appreciate the help you have given me. If it seems like I am a lost cause don't worry about it. If worse comes to worse I will just analyze ALL the records on the list even if it takes me weeks.

[Scott Price]

I tried, but I couldn't get it to work. I am sure its because I'm making a mistake. I think I am making a mistake as to where exactly to plug in these codes. When I am in the form frmTransTime I clicked on the "code" button at the top it brought up the form's code as follows:


[U]Option Compare Database
--------------------------------------------------------------------------------

Private Sub Detail_Click()

End Sub
--------------------------------------------------------------------------------
Private Sub Form_Load()

End Sub
--------------------------------------------------------------------------------
Private Sub Form_Open(Cance l As Integer)
Call TransDiff and Me!txtTransTime = TransDiff lines of code
End Sub
----------------------------------------------------------------------------------
Private Sub List0_BeforeUpd ate(Cancel As Integer)

End Sub
----------------------------------------------------------------------------------
Private Sub Text2_BeforeUpd ate(Cancel As Integer)

End Sub
---------------------------------------------------------------------------------




I plugged in the "Call TransDiff and Me!txtTransTime = TransDiff lines of code" below where it says Private sub form_Open (Cancel As Interger), but it gives me an error box that says Compile Error: Expected: end of statement.


As to the Code (vb) I do not know where to plug that in. As you can tell I am completely new to this side of access and really do appreciate the help you have given me. If it seems like I am a lost cause don't worry about it. If worse comes to worse I will just analyze ALL the records on the list even if it takes me weeks.

Not very many people are lost causes! And most of those who are, want to be...

The error you are getting for the code behind your form is because those two commands need to be on their own lines. So it will look like this:
[CODE=vb]
Call TransDiff
Me!txtTransTime = TransDiff[/CODE]

Now, while you are still in the code window, go to the menu bar at the top, and in the Insert menu, click Module.

In the module that opens, you will notice that it says Option Compare Database at the top of a blank code window. Type in Option Explicit on the line underneath.

Then paste in the function code I gave you in the window below Option Explicit.

Make sure all the names correspond (i.e. frmTransDiff, lstTransDiff, etc. )

Let me know if you need more help after you get these things done!

Regards,
Scott

[Scott Price]

When you get your text box working, you will likely that it won't accept the huge amount of information in it (if you have so many records that it would take you weeks to analyze them manually).

There is another way to show the information, while you are working on getting the text box to work, I'll work on getting the other set up :-)

Regards,
Scott

[Astrom33]

Scott,

Thank you very much for your responses. As I write this I have my other monitor with Access open. So far the code on the frmTransTime did not show me an error after I finished it like before so I have my fingers crossed. I am going to cut and paste all the code and see what happens.

[Astrom33]

Well I closed all the windows and tried to open up the form but received the error "Compile error: Ambiguous name detected: TransDiff."

I'm sure I have to go back and change the name of the form or something so I'll let you know.

[Scott Price]

Well I closed all the windows and tried to open up the form but received the error "Compile error: Ambiguous name detected: TransDiff."

I'm sure I have to go back and change the name of the form or something so I'll let you know.

If you have the TransDiff() function code in two places, it will not know which one you are referring to! Make sure you have only one copy of that code, and it resides in a module of it's own...

Regards,
Scott

[Scott Price]

Hi there, here I am again...

Sorry to lead you a little astray on the first version of this code! I wasn't thinking that you would have too many records to display in a text box. I'm going to be gone most of the day tomorrow, won't be back until the evening, so I'll lay this process out as best I can for you to follow.

1. On the form you created, called frmTransTime, delete the text box named txtTransTime.

2. Place a command button on the form.
a. In form design view, click the command button on the tool bar, click and drag where you want it to be on your form. When the wizard pops up to ask you what you want it to do, click Cancel.
b. right click on the button and choose Properties. Go to the All tab, and rename it something meaningful, like cmdPrint.
c. go to the Event tab, and click the ellipsis (...) next to the On Click event.
d. place your code:

Call TransDiff

on one line, and your code

DoCmd.OpenReport "rptTimeDiff", acViewPreview

on the next line.
e. Compile (Debug menu in VBA editor window) Save changes.

3. Go to the module that you created, and replace the first version with this version of code:
[CODE=vb]Public Function TransDiff() As String

Dim MyArray() As Variant
Dim TransTime As Date
Dim TransTime1 As Date
Dim TimeDiff As Double
Dim TransID As Integer
Dim Temp As String
Dim Temp1 As String
Dim Temp2 As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim ls1 As Integer
Dim intCounter As Integer
Dim list As Integer
Dim list1 As Integer
Dim MySQL As String

Erase MyArray
Set rs = Forms!frmTransT ime!lstTransTim e.Recordset

With rs
.MoveFirst
.MoveLast
ls = .RecordCount
.MoveFirst
MyArray() = .GetRows(ls)
End With

TransTime = MyArray(1, 0)
TransTime1 = MyArray(1, 1)
TransID = MyArray(2, 0)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = "Between Transaction: " & Temp1 & " And Transaction: " & Temp1 + 1 & " are " & Temp & " minutes. "
MySQL = "INSERT INTO tblTimeDiff ([TransID], [TimeDiff]) VALUES ('" & Temp1 & "','" & Temp2 & "');"
DoCmd.SetWarnin gs False
DoCmd.RunSQL MySQL

list = 0
list1 = 1
ls = ls - 2

For intCounter = 1 To (ls)
If list1 <= (ls) Then
list = list + 1
list1 = list1 + 1
TransTime = MyArray(1, list)
TransTime1 = MyArray(1, list1)
TransID = MyArray(2, list)
TimeDiff = DateDiff("n", TransTime, TransTime1)
Temp = TimeDiff
Temp1 = TransID
Temp2 = "Between Transaction: " & Temp1 & " And Transaction: " & Temp1 + 1 & " are " & Temp & " minutes. "
MySQL = "INSERT INTO tblTimeDiff ([TransID], [TimeDiff]) VALUES ('" & Temp1 & "','" & Temp2 & "');"
DoCmd.RunSQL MySQL
End If
Next
rs.Close
Erase MyArray
DoCmd.SetWarnin gs True
End Function
[/CODE]

Compile changes, Save. Close VBA editor window.

4. Create the table called tblTimeDiff with two columns, TransID Number Primary Key, TimeDiff, Text Max size 255.

5. Create a report using the report wizard, base it on the tblTimeDiff, choose to display all fields. Step through the wizard hitting the Next button to accept the default options to the end of the wizard.

Save changes, then test.

Regards,
Scott