Query Question

[jalenben1]

Is there any way to query a vlookup in and SQL statement form a Pass through query? I have a SQL atatement below:

SELECT qryHuaweiTicketsQuery.AssignedToGroup, qryHuaweiTicketsQuery.IncidentID, qryHuaweiTicketsQuery.Node, qryHuaweiTicketsQuery.AssetType, qryHuaweiTicketsQuery.Description, qryHuaweiTicketsQuery.CreatedBy, qryHuaweiTicketsQuery.Status, qryHuaweiTicketsQuery.Priority, qryHuaweiTicketsQuery.Category, qryHuaweiTicketsQuery.Type, qryHuaweiTicketsQuery.ResolutionCategory, qryHuaweiTicketsQuery.ResolutionSubCategory, qryHuaweiTicketsQuery.ResolutionSpecifics, qryHuaweiTicketsQuery.[Occurrence-Date], qryHuaweiTicketsQuery.[Restoral-Date/Time], qryHuaweiTicketsQuery.DateCreated, qryHuaweiTicketsQuery.OutageMinutes, qryHuaweiTicketsQuery.Impact, qryHuaweiTicketsQuery.Owner, qryHuaweiTicketsQuery.Severity, qryHuaweiTicketsQuery.Region, qryHuaweiTicketsQuery.AssignedToFullName, qryHuaweiTicketsQuery.DeviceFirstOccurrence, qryHuaweiTicketsQuery.Service, qryHuaweiTicketsQuery.NEType, qryHuaweiTicketsQuery.AlarmCondition, qryHuaweiTicketsQuery.AcknowledgedTime, qryAlertKeyQuery.AlertKey, DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated]),[qryHuaweiTicketsQuery.DateCreated]) AS WeekStart, DateAdd("d",-Weekday([qryHuaweiTicketsQuery.DateCreated])+6,[qryHuaweiTicketsQuery.DateCreated]) AS WeekEnd, Format([qryHuaweiTicketsQuery.DateCreated],"mmmm") AS [Month], ([qryHuaweiTicketsQuery.DateCreated])-([qryHuaweiTicketsQuery.Occurrence-Date]) AS [MTTR Response], ([qryHuaweiTicketsQuery.Restoral-Date/Time])-([qryHuaweiTicketsQuery.Occurrence-Date]) AS [MTTR Restoral]
FROM qryHuaweiTicketsQuery LEFT JOIN qryAlertKeyQuery ON qryHuaweiTicketsQuery.IncidentID=qryAlertKeyQuery.UNOIncidentID;

Can I create a vlookup in a query that will allow me to automate if a ticket was MET or MISSED?

 

[dhookom]

What are you trying to do? VLookup() is an Excel function. Access supports DLookup(). What does "pass through" have to do with your question?

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

that will allow me to automate if a ticket was MET or MISSED?

There's not enough information about what you want to do to understand that statement. What are the things that you want to happen when a ticket is "automated"? What field tells me whether a ticket was "MET" or "MISSED"?

 

[TheAceMan1]

Formatted for viewing only ...

[blue]SELECT HTQ.AssignedToGroup,
HTQ.IncidentID,
HTQ.Node,
HTQ.AssetType,
HTQ.Description,
HTQ.CreatedBy,
HTQ.Status,
HTQ.Priority,
HTQ.Category,
HTQ.Type,
HTQ.ResolutionCategory,
HTQ.ResolutionSubCategory,
HTQ.ResolutionSpecifics,
HTQ.[Occurrence-Date],
HTQ.[Restoral-Date/Time],
HTQ.DateCreated,
HTQ.OutageMinutes,
HTQ.Impact,
HTQ.Owner,
HTQ.Severity,
HTQ.Region,
HTQ.AssignedToFullName,
HTQ.DeviceFirstOccurrence,
HTQ.Service,
HTQ.NEType,
HTQ.AlarmCondition,
HTQ.AcknowledgedTime,
AKQ.AlertKey,
DateAdd("d",-Weekday([HTQ.DateCreated]),[HTQ.DateCreated]) AS WeekStart,
DateAdd("d",-Weekday([HTQ.DateCreated])+6,
[HTQ.DateCreated]) AS WeekEnd,
Format([HTQ.DateCreated],"mmmm") AS [Month],
([HTQ.DateCreated])-([HTQ.Occurrence-Date]) AS [MTTR Response],
([HTQ.Restoral-Date/Time])-([HTQ.Occurrence-Date]) AS [MTTR Restoral]
FROM qryHuaweiTicketsQuery AS [purple]HTQ[/purple]
LEFT JOIN qryAlertKeyQuery AS [purple]AKQ[/purple]
ON HTQ.IncidentID=AKQ.UNOIncidentID;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jalenben1]

AceMan

I guess I am not being clear enough. I have this query I have created. Within my query I was able to calculae the MTTR (Mean Time to Respond) with the two fields DateCreated and Occurrence-Date. I got myy desired results I was looking for. What I was asking if there was a formula I could use in my query that would tell me if the MTTR that was calculated cold be determined whether the MTTR was MET or MISSED based against the targeted time. For example if the MTTR was 0:13 (mins) and the targeted MTTR is 0:15 (mins) then the ticket was MET. I have the targets below along with the SLA:

SLA Targets
Critical 0:15
Critical (Not Measured) 0:15
Major 0:15
Major (Not Measured) 0:15
Minor 0:35

Another thing I was not probably being clear about is that in my form I created I used a text box that depicts the MTTR which was calculated in my query. I was wondering if I have another text box called KPI (Met/Missed) is there any way to formulate to determine if a MTTR was MET or MISSED based off of the criteria above? I was thinking if I made the above into a combo box and if I have a drill down that I could select one of the SLA's and in the text box called KPI MET/MISSED it would be formulated to show whether a ticket was MET or MISSED. For example....if I clicked on the combo and selected Critical then the text box would show whether a ticket was MET or MISSED. If the MTTR shows 0:13 (mins) and I clicked on CRITICAL in the combo box then the words MET would show since the MTTR of 0:13(mins) did meet the target of 0:15(mins).

I hope that was clear enough

 

[TheAceMan1]

jalenben1 . . .

Maybe I should've been more explicit. My post simply makes the SQL more readable! Looking at it, its easy to see its component parts.

I did this so anyone who comes to this thread won't have to spend any time figuring out the query or what it does. Its easily see at a glance. Its my soul purpose for the thread ... an aid!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]