locked
Creating a data array for determining average of all performance measure parameters RRS feed

  • Question

  • Hello VBA developers more experienced and trained than I,

    VERSION:  MS Access 365 Small Business Premium

    I have 10 measures (fields A-J) of measured performance appraisal on a scale of 1-5 (want data to stay integer so that I can calculate the average of all records on all measures).

    GOAL (from my perspective)

    To develop a data array with all ten fields in it and all records (this is so much easier in MS Excel, it's set up that way) so that I can perform mathematical operations against them, such as overall average, distribution of ratings by vendor and over time, etc.

    Please don't laugh, but this is my attempt at building the VBA script - I don't have much programming training and each time I found an answer at the MSDN forum, it cleared an error message, but created another one:

    Type AllCatArray
            AMgtAdmin(1 To 5) As Integer
            BEqtFac(1 To 5) As Integer
            CSubCont(1 To 5) As Integer
            DPlanSched(1 To 5) As Integer
            EQualProg(1 To 5) As Integer
            FTechComp(1 To 5) As Integer
            GMatProc(1 To 5) As Integer
            HCostCntrl(1 To 5) As Integer
            ISafety(1 To 5) As Integer
            KSaudiztn(1 To 5) As Integer
    End Type
    Sub AllCategoryArray()
    
        Dim Dbs As DAO.Database
        Dim rstTable As DAO.Recordset
        Dim varPerfCatArray() As AllCatArray
        Redim varPerfCatArray() As Double _
        Set Dbs = CurrentDb
        Set AllCatArray = Nothing
        Set rstTable = Dbs.OpenTable(",", params[A][B][C][D][E][F][G][H][I][K] FROM dbs.[Join 1]", dbOpenTable)
        varPerfCatArray() = rstTable.GetRows
        rstTable.Close
        
    End Sub

    Being that I have some limited background in MS Access and VBA and the person that will maintaining the system after I leave will likely be EVEN LESS SO, I want to keep the code as simple as possible.

    I greatly appreciate your input and advice for the simplest solution to my goal,

    LFMC "Lawrence C. of Arabia"

    Friday, October 17, 2014 12:00 PM

All replies

  • Hi,

    Thank you for posting in the MSDN Forum.

    Since the issue is more related to the feature of VBA, I'd like to move it to VBA forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 20, 2014 8:09 AM