Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Compare field values across rows/records

Discussion in 'Information Technology' started by Sarah, Jul 28, 2006.

  1. Sarah

    Sarah
    Expand Collapse
    Guest

    Hello -

    I need to do calculations across values in the same field across multiple
    rows in an Access query. For each account number I've got, there are several
    transactions, and an interest rate is shown for each transaction. So, I
    might have 5 transactions for account 111, two at a 9.5 interest rate, and
    three at a 10.00 interest rate. If the interest rate changes over the range
    of transactions for a single account, I need to determine the date of the
    last transaction at the first interest rate. So, I need to compare the
    values of the interest rates across rows of data, and then pull the
    transaction date out of the row/record with the last instance of the lower
    rate. Is this possible to do in an Access query? If not, any other
    suggestions on how to obtain the information?
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Does the table include a date/time field and, if so, does it contain just the
    date?

    How do you define the "first interest rate"?

    What are the actual table and field names?

    Is the interest rate field a number or text datatype?

    Account InterestRate Date
    1111 9.5 1-April-2006
    1111 10.0 1-May-2006
    1111 9.5 1-June-2006
    1111 10.0 1-July-2006
    1111 10.0 1-August-2006

    Which of the above records do you want returned?
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Sarah" wrote:

    > Hello -
    >
    > I need to do calculations across values in the same field across multiple
    > rows in an Access query. For each account number I've got, there are several
    > transactions, and an interest rate is shown for each transaction. So, I
    > might have 5 transactions for account 111, two at a 9.5 interest rate, and
    > three at a 10.00 interest rate. If the interest rate changes over the range
    > of transactions for a single account, I need to determine the date of the
    > last transaction at the first interest rate. So, I need to compare the
    > values of the interest rates across rows of data, and then pull the
    > transaction date out of the row/record with the last instance of the lower
    > rate. Is this possible to do in an Access query? If not, any other
    > suggestions on how to obtain the information?
     

Share This Page