Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where is list of Query Match Functions? (Access XP SP2)

    While I am specifically looking for how to find the minimum of 10 different numeric fields within a query (not using VBA), I can't seem to find a list of all the math functions for Access including sum, average, minimum, maximum, standard deviation, etc. I've looked at the Access help file and in the knowledgebase but didn't find a simple list.
    Any help for a novice will be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where is list of Query Match Functions? (Access XP SP2)

    In the help file, select the Contents tab; expand Microsoft Access Help > Programming in Visual Basic > Visual Basic Reference > Functions. You will find a list of general VBA functions. In Access 97 help, there was a list of all functions available in Access (including specific Access functions), but I don't know if this can be found in Access 2002 help.

    Aggregation functions such as Min and Max can only be used across records, not across fields. What do you mean by "the minimum of 10 different numeric fields"? Do you want to find the minimum value of each field separately, or the minimum over all ten fields?

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is list of Query Match Functions? (Access XP SP2)

    Thank you for your speedy reply.
    To be more precise than I was with my original message, I am trying to take 10 separate fields within a single query and find the lowest value of the 10 for each of the 100 total records I'm analyzing - thus leaving me with 1 new value per record. I hope this is a better explanation.
    I'm not sure if my terminology is accurate. If I am trying to use a function such as AVG within a query in Access, is that considered to be a part of Visual Basic?
    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where is list of Query Match Functions? (Access XP SP2)

    Some of the functions you can use in queries are part of the query language SQL; examples are Sum, Count, Min, Max and Avg. These functions always work across records, i.e. they calculate the sum, count etc. of a field or expression across all records in a query. You can also use Visual Basic functions in queries, whether built in, such as IIf, Abs, Chr and Date, or user-defined. There is no equivalent of Excel functions such as MIN - as mentioned, the SQL function Min works across records, you can't use it to find the minimum value of a series of fields within one record.

    To do what you want, you must use an awful expression with nested IIf functions, if you don't want to use a user-defined VBA function:

    MinimumValue: IIf([Field1]<[Field2],[Field1],IIf([Field2]<[Field3],[Field2],IIf([Field3]<[Field4],[Field3],IIf([Field4]<[Field5],[Field4],IIf([Field5]<[Field6],[Field5],IIf([Field6]<[Field7],[Field6],IIf([Field7]<[Field8],[Field7],IIf([Field8]<[Field9],[Field8],IIf([Field9]<[Field10],[Field9],[Field10])))))))))

    where Field1 etc. are the field names.

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is list of Query Match Functions? (Access XP SP2)

    Thanks for all the info.
    Wow! Quite an intricate way to do what I want. Thanks for taking the time to give me an example or I would never have figured it out.
    I wonder if the new version of Access will include what should be a fairly basic MINIMUM function that I would have to believe more than a few people would use.
    Thanks again!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Where is list of Query Match Functions? (Access XP SP2)

    Keep in mind that spreadsheet functions are designed to work on spreadsheets. Even though an Access table or query may look like a spreadsheet, the relationship among the values in the "cells" is not fixed as it is in Excel.
    Charlotte

Posting Permissions

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