Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    11

    Unanswered: Need to exclude certain metrics

    Need to exclude certain metrics

    Hi all I was hoping I could get some help here please.
    I have a data input xls file
    The purpose of the file is to have employees enter their metrics into a specific row
    Once they are done the metric is then to be saved in the Access database.
    What I would like help with is(picture the following like a single row with 6 columns
    How would I get Metric #1 and Metric #5 to continue as is but to EXCLUDE Metrics #1, Metrics #2,
    and Metrics #3,
    __________________________________________________ ____________________________
    Date | Metric #1 | Metric #2 | Metric #3 | Metric #4 | Metric #5 |
    __________________________________________________ ____________________________


    This is my code so far


    ''--------------------------------------------------------------------------------------------------------------------------------------------
    '
    'Private Sub Worksheet_Change(ByVal Target As Range)
    'Dim MDate As String
    'Dim MMetric As String
    'Dim MTeam As String
    'Dim MValue As Double
    'Dim MSheet As String
    'Dim Dbs As Database
    'Dim RST As DAO.Recordset
    'Dim DBSPath As String
    'Dim DBSName As String
    'Dim Msql As String
    '
    'On Error Resume Next
    '
    ''--------------------------------------------------------------------------------------------------------------------------------------------
    '
    ' If Target.Row > 2 And Target.Row < 64 Then
    ' MTeam = Worksheets("Info_Input").Cells(2, 2).Value
    ' ElseIf Target.Row > 74 And Target.Row < 135 Then
    ' MTeam = Worksheets("Info_Input").Cells(73, 2).Value
    ' ElseIf Target.Row > 145 And Target.Row < 206 Then
    ' MTeam = Worksheets("Info_Input").Cells(144, 2).Value
    ' ElseIf Target.Row > 216 And Target.Row < 277 Then
    ' MTeam = Worksheets("Info_Input").Cells(215, 2).Value
    ' ElseIf Target.Row > 287 And Target.Row < 348 Then
    ' MTeam = Worksheets("Info_Input").Cells(286, 2).Value
    ' ElseIf Target.Row > 358 And Target.Row < 419 Then
    ' MTeam = Worksheets("Info_Input").Cells(357, 2).Value
    ' ElseIf Target.Row > 429 And Target.Row < 490 Then
    ' MTeam = Worksheets("Info_Input").Cells(428, 2).Value
    ' ElseIf Target.Row > 500 And Target.Row < 561 Then
    ' MTeam = Worksheets("Info_Input").Cells(499, 2).Value
    ' End If
    ' If UCase(MTeam) = "ALL" Then
    ' Exit Sub
    ' End If
    ' If UCase(MTeam) = "File & TERM ADMIN TEAM" Then
    ' MTeam = "File AND TERM ADMIN"
    ' End If
    '
    ' MTeam = Trim(Replace(MTeam, "TEAM", ""))
    ' MDate = Range("B" & Target.Row)
    ' MMetric = Cells(2, Target.Column)
    ' MValue = Target
    ' MSheet = "Data"
    ' Dim Mmetric_ID As Double
    '
    '
    ' ActiveWorkbook.Save
    '
    '
    '
    '
    ' Msql = " SELECT Metrics.Metric, Reporting_Hierarchy.Level_1, Metrics_X_Reporting_Hierarchy.Metric_ID, Data_Monthly.Date, " _
    ' & "Data_Monthly.Value " _
    ' & "FROM ((Metrics_X_Reporting_Hierarchy INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID) " _
    ' & "INNER JOIN Reporting_Hierarchy ON Metrics_X_Reporting_Hierarchy.Hierarchy_ID = Reporting_Hierarchy.Hierarchy_ID) " _
    ' & "INNER JOIN Data_Monthly ON Metrics_X_Reporting_Hierarchy.Metric_ID = Data_Monthly.Metric_ID " _
    ' & "WHERE (((Metrics.Metric)='" & MMetric & "') " _
    ' & "AND ((Reporting_Hierarchy.Level_1)='" & MTeam & "') " _
    ' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
    '
    '
    '
    '
    '
    '
    'DBSPath = "C:\Documents and Settings\db"
    'DBSName = "codb.mdb"
    '
    'Set Dbs = OpenDatabase(DBSPath & "\" & DBSName)
    ''
    'Set RST = Dbs.OpenRecordset(Msql)
    'If Not RST.EOF Then
    ' 'record exists, find the record in the data_montly table and edit the value of the existing record
    '
    ' If Trim(Target) <> "" Then 'edit the record
    ' Mmetric_ID = RST!metric_ID
    ' Set RST = Nothing 'want to reuse the variable - need to clear it out.
    ' Msql = "SELECT Data_Monthly.Metric_ID, Data_Monthly.Date, Data_Monthly.Value " _
    ' & "FROM Data_Monthly " _
    ' & "WHERE (((Data_Monthly.Metric_ID)= " & Mmetric_ID & ") " _
    ' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
    ' Set RST = Dbs.OpenRecordset(Msql)
    ' RST.MoveFirst
    ' RST.Edit
    ' RST!Value = MValue
    ' RST.Update
    ' Set RST = Nothing
    ' Else 'delete the record
    ' Mmetric_ID = RST!metric_ID
    ' Set RST = Nothing 'want to reuse the variable - need to clear it out.
    ' Msql = "SELECT Data_Monthly.Metric_ID, Data_Monthly.Date, Data_Monthly.Value " _
    ' & "FROM Data_Monthly " _
    ' & "WHERE (((Data_Monthly.Metric_ID)= " & Mmetric_ID & ") " _
    ' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
    ' Set RST = Dbs.OpenRecordset(Msql)
    ' Set RST = Dbs.OpenRecordset(Msql)
    ' RST.MoveFirst
    ' RST.Delete
    '
    ' Set RST = Nothing
    ' End If
    '
    'Else
    ' 'record doesn't exist. Find the metric_ID in the CR table - if the metric id is found, insert the record into the data_monthly table
    ' If Trim(Target) <> "" Then
    ' Msql = "SELECT Reporting_Hierarchy.Level_1, Metrics.Metric, Metrics_X_Reporting_Hierarchy.Metric_ID " _
    ' & "FROM (Reporting_Hierarchy INNER JOIN Metrics_X_Reporting_Hierarchy ON Reporting_Hierarchy.Hierarchy_ID = Metrics_X_Reporting_Hierarchy.Hierarchy_ID) " _
    ' & "INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID " _
    ' & "WHERE (((Reporting_Hierarchy.Level_1)= '" & MTeam & "') " _
    ' & "AND ((Metrics.Metric)= '" & MMetric & "'));"
    '
    '
    ' Set RST = Dbs.OpenRecordset(Msql)
    ' RST.MoveFirst
    ' Mmetric_ID = RST!metric_ID
    ' Set RST = Nothing
    ' Msql = "Select * from Data_Monthly"
    ' Set RST = Dbs.OpenRecordset(Msql)
    ' RST.AddNew
    ' RST!Date = MDate
    ' RST!metric_ID = Mmetric_ID
    ' RST!Value = MValue
    ' RST!Status = "Active"
    ' RST.Update
    ' Set RST = Nothing
    ' End If
    'End If
    '
    '
    '
    '
    'End Sub

  2. #2
    Join Date
    Mar 2010
    Posts
    22
    Quote Originally Posted by mxtreme View Post
    How would I get Metric #1 and Metric #5 to continue as is but to EXCLUDE Metrics #1, Metrics #2, and Metrics #3,
    You obviously mean to exclude Metric #2, #3 & #4 right?

    When you say exclude, do you just want them hidden from the user or removed altogether?

    Are there criteria for excluding these Metrics?

    Dan

  3. #3
    Join Date
    Apr 2010
    Posts
    11
    I solved it via

    If MTeam = "CUSTOMER SERVICE" And MMetric = "Service Response" Then
    ActiveWorkbook.Save

    ElseIf MTeam = "CUSTOMER SERVICE" And MMetric = "Service Response" Then
    ActiveWorkbook.Save

    Else



    Quote Originally Posted by danerida View Post
    You obviously mean to exclude Metric #2, #3 & #4 right?

    When you say exclude, do you just want them hidden from the user or removed altogether?

    Are there criteria for excluding these Metrics?

    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •