Results 1 to 3 of 3
  1. #1
    chetana is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    11

    How to write a query find Min mum number in comparing with other fields numbers?

    Hi,



    How to write a query to get minimum number by comparing with other fields?

    I have example in excel as shown below
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	56.0 KB 
ID:	42017
    Filds:Q R1,Q R2,Q R3,Q R4,Q R5 and Running Price
    Running Price==MIN(AO4:AS4) Means Comparing Q R1,Q R2,Q R3,Q R4,Q R5 and getting minimum value from that fields.

    Can anyone suggest me,How to do it in access?
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  2. #2
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    this is not a question that can be answered very well without knowing a bit more about your data structure.

    however, in general, there is a Min() function in ACCESS but it will only work on a single field, which means that values have to be in one column of a table rather than in one row as they are in your spreadsheet.

    therefore, to get this to work in ACCESS you will need a table with three fields: costInPart, quarter, price,

    then an AGGREGATE query to group by costInPart, and Min(price) will get you the answer you require.


    good luck with your project,



    Cottonshirt

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I use these simple functions to get the min or mas between two fields, you can nest them to get yours:
    Code:
    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
      Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
      Max2 = IIf(a > b, a, b)
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-30-2017, 07:11 AM
  2. Replies: 1
    Last Post: 01-18-2015, 12:01 PM
  3. Comparing Columns of numbers with IIf function
    By emspence in forum Queries
    Replies: 7
    Last Post: 02-23-2013, 01:05 AM
  4. Find First number in sequence of numbers
    By losstww in forum Queries
    Replies: 4
    Last Post: 11-16-2012, 02:44 PM
  5. Getting wrong answer when comparing numbers
    By cowboy in forum Queries
    Replies: 7
    Last Post: 05-18-2010, 01:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums