Need Help ith Query

[LutonBarry]

Folks Can you help. I need to measure for each Ticket Id, or each Activity Type ID ("HARDWARE-XX-02" or "HARDWARE-UK-03"), return the earliest Start DateTime. If those Activity Type Id's are not present the earliest Start DateTime of the Ticket Id. Then also I need to measure for each Ticket Id, or each Activity Type ID ("HARDWARE-XX-04" or "HARDWARE-UK-05"), return the Latest Resolve DateTime. If those Activity Type Id's are not present the latest Resolve DateTime of the Ticket Id.

I attach a spreadsheet from which the data highlighting the cells I would like to be included in the measure. Note also the HARDWARE-UK and HARDWARE-XX Activity Types will not be included within the same Ticket ID.

I would like the data presented if possible as below.

there is a closed thread Help Needed for a Novice (http://www.vbaexpress.com/forum/showthread.php?54284-Help-Needed-for-a-Novice) with further back ground information.




Ticket Id
Activity Type Id
Start Datetime
Resolved Datetime




1
HARDWARE-XX-02
30/06/2015 09:26:25
12/07/2015 09:20:00




2
HARDWARE-XX-02
27/08/2015 13:48:09
23/10/2015 10:07:00




3
LEVEL1
27/08/2015 14:39:27
18/09/2015 11:36:00

[jonh]

I added your test data to table called "tblactivity".

This prints to the immediate window. I didn't add "Activity Type Id" to the output because I'm not sure what it's supposed to refer to.



Private Sub Command0_Click()
Debug.Print "Ticket Id", "Start Datetime", "Resolved Datetime"
With DBEngine(0)(0).OpenRecordset("select distinct [Ticket Id] from tblactivity")
Do Until .EOF
Debug.Print .Fields(0), sdate(.Fields(0), 1), sdate(.Fields(0), 2)
.MoveNext
Loop
.Close
End With
End Sub


Private Function sdate(id As Long, i As Byte) As Date
Select Case i
Case 1
fld = "Start Datetime"
mch = "HARDWARE-XX-02;HARDWARE-XX-03"
ord = "asc"
Case 2
fld = "Resolved Datetime"
mch = "HARDWARE-XX-04;HARDWARE-XX-05"
ord = "desc"
End Select

With DBEngine(0)(0).OpenRecordset("select [Activity Type Id],[" & fld & _
"] from tblactivity where [Ticket Id]=" & id & " order by [" & fld & "] " & ord)
sdate = .Fields(1)
Do Until .EOF
If InStr(1, mch, .Fields(0), vbTextCompare) Then
sdate = .Fields(1)
Exit Do
End If
.MoveNext
Loop
.Close
End With
End Function

[LutonBarry]

Jonh, Thanks for your help. I tried it but I get a Compile error at the first 'With DBEngine(0)(0).OpenRecordset' instruction.

I've save the data into a Table as you instructed and pasted your code into the VBA module.

[jonh]

what's the message?

Check your table an field names.