Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Conditioned Average

    Hi!

    I have encountered a problem and i don't know how to solve it. A little help maybe ... please.

    I have a Querry that is named "Average" which contains the fields MARK1, MARK2, MARK3, MARK4, MARK5 and the field AVGMARK. I want to calculate the average between MARK1, MARK2, ..., MARK5 in AVGMARK, but these fields can all be populated (5) or 4, or 3, and so on, but minimum 1 field (at least one field must be populated).

    Hope you can help. Thank you!

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You cannot use the AVG function in a query to work the average across columns you must use code.

    In the AVGMARK column of your query put the following
    avgmark: calculate_avg([mark1],[mark2],[mark3],[mark4],[mark5])

    Now create a module with the following code

    This will calculate the average of the marks that are passed in as parameters

    Function calculate_avg(mark1, mark2, mark3, mark4, mark5) As Double

    Dim total_marks As Integer
    Dim total_entries As Integer

    total_entries = 0
    total_marks = Nz(mark1) + Nz(mark2) + Nz(mark3) + Nz(mark4) + Nz(mark5)

    If IsNull(mark1) Then
    total_entries = total_entries
    Else
    total_entries = total_entries + 1
    End If

    If IsNull(mark2) Then
    total_entries = total_entries
    Else
    total_entries = total_entries + 1
    End If

    If IsNull(mark3) Then
    total_entries = total_entries
    Else
    total_entries = total_entries + 1
    End If

    If IsNull(mark4) Then
    total_entries = total_entries
    Else
    total_entries = total_entries + 1
    End If

    If IsNull(mark5) Then
    total_entries = total_entries
    Else
    total_entries = total_entries + 1
    End If

    If total_entries = 0 Then
    calculate_avg = 0
    Else
    calculate_avg = total_marks / total_entries
    End If

    End Function

  3. #3
    Join Date
    Jun 2009
    Posts
    3

    Records sorting

    Thank you very much for your help!
    It worked great.

    I have one more question. After applying the function you indicated I created a querry called " Scores" with the following structure:

    POINTS NAME OPTION1 OPTION2 OPTION3

    8 John 2 1 3

    9 Robert 1 2 3

    10 Willy 3 1 2

    7 Wu 1 2 3
    and so on

    or otherwise

    POINTS NAME 1 2 3

    8 John OPTION2 OPTION1 OPTION3

    9 Robert OPTION1 OPTION2 OPTION3

    10 Willy OPTION2 OPTION3 OPTION1

    7 Wu OPTION1 OPTION2 OPTION3
    and so on

    where OPTION1, OPTION2 and OPTION3 with values 1, 2, 3 are the preferences of the candidates for the options with interest value of 1, 2 or 3.

    OPT1 has a maximum of X winners
    OPT2 has a maximum of Y winners
    OPT3 has a maximum of Z winners


    I want to order the candidate in the following way:

    Each candidate must be tested separately according to the score obtained and expressed choices, if it can occupy a place in the first option is taken, if in that option there are no longer places it must be tested if he can occupy a place on the second option, if there are places on this option he will be assigned to this option, and if there aren't any places it must be tested if he can occupy a place in the third.
    Just proceed with the next candidate and repeat the operations from the first option.

    Thank you anticipated for your help!

Posting Permissions

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