Query Form Help

[jalenben1]

I have in my form a text box where I need to have populated if a ticket was Met or Missed.

In my form I have 2 text boxes with formulas in my query that gives me both the MTTR Response and MTTR Restoral

MTTR Response Time

([qryHuaweiTicketsQuery.DateCreated])-([qryHuaweiTicketsQuery.Occurrence-Date]) AS [MTTR Response],

MTTR Restoral Time
([qryHuaweiTicketsQuery.Restoral-Date/Time])-([qryHuaweiTicketsQuery.Occurrence-Date]) AS [MTTR Restoral]

I need to have a formula that will tell me if a ticket was Met or Missed accordingly based of the following below:

MTTR Response Time

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

MTTR Restoral Time

Critical 4:00
Critical (Not Measured) 4:00
Major 8:00
Minor 12:00
Minor 13:00
Minor 14:00
Minor 15:00
Minor 16:00
Minor 17:00
Minor 18:00

I have a combo box with a formaula I created:

SELECT [SLA Criticality].[SLA Criticality], [SLA Criticality].[Reponse Time], [SLA Criticality].[Restoral Time] FROM [SLA Criticality] ORDER BY [SLA Criticality].[SLA Criticality].

In the combo box I have a drop down list with all of the categories for both MTTR Response and RESTORAL (Critical, Critical (Not Measured), Major, Major (Not Measured), Minor)

The formula I have for my combo box is:

SELECT [SLA Criticality].[SLA Criticality], [SLA Criticality].[Reponse Time], [SLA Criticality].[Restoral Time] FROM [SLA Criticality] ORDER BY [SLA Criticality].[SLA Criticality];

Based off the above criteria I need to have that formulated that will give me if a ticket was MET or MISSED. For example if the the MTTR Response tome for a ticket was 0:08 minutes and it fell in the 0:15 target then the ticket was MET. If the ticket fell above the 0:15 minute target the the ticket was MISSED. If you click on the combo box drop down list and select one the criterias then the ticket should populate if it was Met or MISSED. Not sure if my formulas are correct. Any suggestions?

 

[jalenben1]

Another question regarding what I sent.....would it be better to have the query to show if a ticket was Met or Missed? Below if my SQL statement

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;