Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Calculation: Left-to-Right? (AXP)

    Before I put together a test case...

    A query I am about to develop produces calculated values. Some of the calculated fields rely on results "in other columns". e.g.,
    Field3 = Field1+Field2, Field4=2*Field3.

    What if I change the field order, and write it like this:
    Field3=2*Field4, Field4 = Field1+Field2 ?

    Will either of those work?

    Or do I need to write two queries, one to calc (the original) Field3, and a second query to calc Field4?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Calculation: Left-to-Right? (AXP)

    In general, as long as the field that depends on another calculated value is in a column to the right of the one it depends on, it will work. It seems to me there are some exceptions, but I don't recall what they were - I think it may involve using functions in the first calculated value. Maybe someone else can dredge up what the issue was.
    Wendell

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Calculation: Left-to-Right? (AXP)

    Why not use Field4=2*(Field1+Field2) and avoid any possibility of a problem? I don't know about 2000 or XP, but in some older versions you could not use a calculated field as a parameter in the same query that calculated it.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Calculation: Left-to-Right? (AXP)

    If my situation was as simple as I stated it, I would do what you suggest. However, many of the values that are calculated are returned from functions. The fields and functions are scavenged from a spreadsheet, where the calculation order is "automatic": cells are recalculated depending on whatever has changed.

    Perhaps another question is pertinent here: my approach to a "complicated expression" is to encapsulate that calculation in a function, verify it outside of the query, and then expose the function interface to the world. Is this the recommended database practice?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Calculation: Left-to-Right? (AXP)

    -------
    Perhaps another question is pertinent here: my approach to a "complicated expression" is to encapsulate that calculation in a function, verify it outside of the query, and then expose the function interface to the world. Is this the recommended database practice
    ------

    For complicated expressions, that's probably a good approach, though it may take a bit more resources during execution. For simple expressions using existing functions and simple IIF statements, we usually embed them in the query or the control.
    Wendell

Posting Permissions

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